adding a defined amount of records to an access table

  • Thread starter Thread starter Darren
  • Start date Start date
D

Darren

Help

I am fairly new to access and need assistance. I have been asked by my
employer to create a database table with two fields (one an autonumber
and one todays date) which i have done.

What i need is a way to add new records to this table by using
macro/vba. The user needs to enter a number which is then used to
update the table by the same amount of records as the number. I will
then export the updated records into a text file.


Any help would be greatly appreciated.

Darren King
 
Create a form in design view and add a text box to the form and name it
txtUserNumber. Double-click on the text box to bring up Properties
window, go to Events tab. Choose AfterUpdate event and select [Event
procedure]. Click on the [...] button next to [Event procedure]. In the
VBA editor, type:

DoCmd.RunSQL "INSERT INTO MyTable SELECT TOP " & Me.txtUserNumber & " 0,
Date() FROM MyTable"

This should work (I didn't test it) as long as the number of new records
requested is smaller than the number of already existing records in the
table. I assumed that the first field is the Autonumber and the second
is the today's date.
Code above does not check the user entry and will fail if user enters a
non-integer or alphabetic value. You may want to add a check for this to
the code. You also may want to set Warnings in DoCmd to Off and back to
On to avoid seeing the Update message.
Hope this helps,
Pavel
 
Back
Top