Appending empty rows (1 Viewer)

Access_Help · Jan 17, 2019

I want the user to select the number of 'entries' required' from a comboxbox, lets say the user selects 3, after clicking a button , it will add three empty rows to the table.

I'm guessing this can be achieved through VBA?

Any pre-written script will be appreciated.

theDBguy · Jan 17, 2019

Yes, it’s possible. But first, can you tell us why you need to do this? Just curious...

Access_Help · Jan 17, 2019

An exam requires 1 invigilator per 30 students. If there are 120 students (a calculated field will work out the number of invigilators). I require one row for each invigilator required.

isladogs · Jan 17, 2019

Assuming your table has an autonumber PK field and at least one text field, one way to do this is to create a procedure to add a null value to the text field

Code:

Sub AddEmptyRecords()

Dim N As Integer

"loop through and add required number of empty records
For N = 1 to 3 'example value 
'For N =1 to Forms!FormName.ControlName 'Normally get the number of loops from a form control
   CurrentDb.Execute "INSERT INTO Table1 ( TField ) SELECT Null AS TField;"
Next

End Sub

However, surely if you know how many invigilators you need, then you know when you need them (exam date). So I would populate at least that one field ready to add invigilator names later

Galaxiom · Jan 17, 2019

An radically alternative approach can be found in Post 3 on this thread

It uses an outer join to make virtual records appear on a form that only become present in the table when the other data is filled into a record. The technique could be used to make all relevant invigilators automatically appear as virtual records but only write records of that are selected by a user.

isladogs · Jan 17, 2019

Thanks Greg
That's a neat solution which I've also used in the past for a similar situation

Appending empty rows (1 Viewer)

Access_Help

Registered User.

theDBguy

I’m here to help

Access_Help

Registered User.

isladogs

MVP / VIP

Galaxiom

Super Moderator

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread