limit records in a table by using VBA code


I would like to find a way to limit records in a table by using VBA code. I have a couple of tables that contain data specific to the user (licensed). It is my understanding that these are much safer if entered in code vs. sitting out in a table where clever users may track them down.

For example, letís say I only wanted to allow 3 records into tblMasterTable. I can set the validation rule (in design view of the table) to have the PrimaryKey (integer): > 0 And


Sponsored Links:



I would like to find a way to limit records into a table by using VBA code. I have a couple of tables that contain data specific to the user (licensed). It is my understanding that these are much safer if entered in code vs. sitting out in a table where clever users may track them down.

For example, letís say I only wanted to allow 3 records into tblMasterTable. I can set the validation rule (in design view of the table) to have the PrimaryKey (integer): > 0 And




I hope everyone's having a good Friday. Thank you kindly to anyone kind enough to offer some advice...

I have a table of attendance data for children attending a club. This table (T_Attendance) records the AttendanceID (autonumber) ChildID, Date and whether they attended or not. I'd like to use this data to create a log of when each child started / stopped and restarted coming to the club. This data should be recorded in a table called T_Actions, which records:
ActionID (autonumber, primary key) ActionChildID (number) ActionDate (date) ActionType (number: 1 for started, 2 for quit, 3 for restarted) I would like to create a query or piece of VBA code (I'm not fussy which, it only needs to run once), which for each Child will go through the attendance records in order of date, and perform the following actions:
If the current record is the final record for this child AND the date is more than 30 days earlier than NOW, Append a record to T_Actions to say child has quit (use the day after the current record's date as ActionDate). If the current record is not the final record for this child AND the difference between the date of the current record and the date of the next record is greater than 40 days Update T_Actions to say child quit (use the day after the current record as the date). Add a further record showing that they restarted on the date of the following record. I only have accurate attendance records in the database since 1st July 2009, so this query should ignore any records with dates prior to that.

I get the feeling this should be quite simple to accomplish, however I'm not very sure how to begin with this so I'd really appreciate any help.

Many Thanks,

Alan




How can I go about accessing records in a table/query from within VBA? I would not be using modules as I don't know how they work, it would be some code attached to a control on a form.

Also, the table/query being accessed would not be the one that is the Record Source of the form on which the control is situated.

I would want to locate a particular record and then be able to access a particular field.

I'm trying to be as broad reaching in my description here because I don't have that much experience with VBA (for Access 2k, using Access XP) and need to keep things simple...

If there's a thread in here that deals with what I need I can't find it but then there's a lot of information and I'm probably using the wrong search criteria...

Anyway, thanks in advance for any useful replies.

Tim




I have a simple table of Customer names. It's as simple as two fields involved:

CustomerID
CustomerName

How can I use VBA code or macro to add a new record to this table? I'm a newbie, so if possible, please provide a few lines of example codes. Thanks.




I have a table that contains financial data on projects. Each project has one record consisting of numerous fields.

I want users to be able to archive off copies of these records at certain points in time so I can track the financial data - a bit like storing different versions or issues.

On any one day I want users to be able to access the live record and review the archive.

Question : Is there a simple way of duplicating a record in a table ?

At the moment I am using a form by copying all of the field values from this form, moving to a new record, writing the field values back to the form hence creating a copy. This seems somewhat laborious.

Can't I access the table directly somehow and simply cut and paste ?

Also, is there a simple way to check for a duplicate record before cloning the record i.e. if the data has already been archived once don't do it again.




Hi,
I am using Vb6.0 as a front end and msaccess as the db.
i want 2 delete all the records in a table "Register" by clicking a menu
The connection is made using ADODB
Till now i hv the code

Private Sub mnudel_Click()
Docmd.SetWarnings False
Docmd.RunSql ("DELETE * FROM Register;")
Docmd.SetWarnings True
End Sub

But when i execute this a error msg displays
Run time error "424"
Object required

Whats the problem in me
plz help me to come out from this...




Hi,
I am using Vb6.0 as a front end and msaccess as the db.
i want 2 delete all the records in a table "Register" by clicking a menu
The connection is made using ADODB
Till now i hv the code

Private Sub mnudel_Click()
Docmd.SetWarnings False
Docmd.RunSql ("DELETE * FROM Register;")
Docmd.SetWarnings True
End Sub

But when i execute this a error msg displays
Run time error "424"
Object required

Whats the problem in me
plz help me to come out from this...




Hi

I'm using a form (frmSearchSurname) to search for records in a table (tblContacts) with a query being the record source (qrySurnameSearch). In the query the criteria is on the Surname field Like [Enter Surname] & "*"

On the form properties I also have the Filter as ((((tblContacts.Surname) Like [Enter Surname] & "*")))

It all works fine when I load the form, it prompts for the surname of a contact and does find the contact or contacts ok

However, if the contact surname does not exist, I just get a blank form without any of the control buttons or fields on there so the user cannot click on my exit button to return to the previous menu

Any idea on how I can get something like a msgbox to say 'No data found' and return to the previous form?

Many thanks




Can somebody point me in the right direction
for some VBA code to delete all records
in a table. I know I can create a macro and call the macro,
but like to do it in VBA

Thanks for any help




I need to create a summary total of a field for groups of records in a table grouped by a "class" field in the table...then I need to update fields in the same table with the total for each group... can anyone help....thx




I need to make Access automatically create new records in a table for me.

Just for example:-

Table #1 has 2 fields

PersonID (autonumber)
PersonData (text)

Table #2 has 3 fields

ID (autonumber)
PersonID (number)
MoreData (text)

The database user creates a new record in Table #1 using a form.

I need the database to automatically create a field in Table #2 and fill in PersonID, taking the value from the autonumber field of the same name in Table #1.

The MoreData field can be left blank. I don't need that filled in automatically. I just need a new record to be created automatically in table2 with the PersonID field filled in with the most recently created autonumber.

Help massively appreciated.




first of all i introduce. i am a novice and doing one very small application in access using VBA.
i have a table called "input" in a file called "book1"and i can export it using the code.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "input", "z:Book1.xls", False, "Sheet1$"

it works fine as it exports the data.
i have couple of problems if someone could help me with that it will be great.

1. whenever it exports the data it makes the same number of blanks where the old data file was.
so after exporting few times there are hell of blank lines on top.
2. when i export data it exports all the data as "Text" format but i have few items as number in my access table.
is there a way that i can export the data in the same format as the actual table in access is.
3. anyway i can email the table to myself using VBA code.

as i mentioned i am new to Access databases and VBA . help will be much apreciated.

Regards

A learner




Is there a way to update all records in a table with the information currently entered on a form in single form view (bound form that updates the table whe the form is updated). For example, when I'm on the form, I see a single record at a time, but I would like to have a button that updates all records in the table when clicked.

Do I need to cycle through each record in a loop or is there something like all.field I could use (as opposed to me.field, which is used to only update the current record)?




Greeting all,
I have a db where I am wanting to reset or clear the values for specific fields for all records in a table. For example, I have a vendor table that tracks the amount of marketing dollars each vendor has committed. At the end of the year, I want the DB user to be able to hit a button and reset that value to $0.00. in the vedor table. I have several fields like this and would like to automate this task with vb code or a macro. I don't know much vb code but I can copy and paste like a mad man. Can anyone help




Is there a macro that will delete all the records in a table? Ive tried a few different ones but they're not what im looking for.


Thanks,
Matt




Hi, I want to delete all the records in a table after sending these records to a report named as "MonthlyBalance" when I click a button. I written the following codes and I knew there is something wrong but I don't know where... can someone plse help me out with this? Here, I assume the field "BookTitle" will not contain "*" so all the records should be deleted...
Or is there a more correct way to delete all the records..?

Set rst = db.OpenRecordset("Select * From BalanceTable", dbOpenDynaset)
stDocName = "MonthlyBalance"
DoCmd.OpenReport stDocName, acPreview

wrk.BeginTrans
Do Until rst.EOF
If (rst!BookTitle <> "*") Then
rst.Delete
rst.Update
End If
rst.MoveNext
Loop
wrk.CommitTrans

Thanks!




Dear,

In the middle of data entry in a MS Access form, If the user press cancel button, the updated record in a table must be undo.
Ho it is possible.
I can delete the newly entered record, but i need to undo the updated record also.




I have a form with text boxes that are updated with their values from other forms or dlookups. I want to take that information on the form and create a record in a table with that data.
Whats the Best / easiest way to do this?




Hi all,
Can you change the color of lines of records in a table in dependence from a check box? So if you list it in a specific subform or something like that it will be a different color? Is there another way of doing this and is there an example?
Thanks