Click a button and get 24 blank records with the # column populated

[ruthcali]

i am using Access 97.

i have two tables: one called tblEquip and one called tblSystems.

one piece of Equipment can hold 24 Systems.

i have a form with data from tblEquip as the main form, and a subform datasheet with data from tblSystems.

when the user has a brand new piece of equipment to add to the database, he clicks a button called 'Add new Equipment'.

On the subform, i have a field called Num#. I would like that field to self populate with the numbers 1 thru 24.

so the subform would have 24 new records created and those 24 new records would already have a value in the Num# column.

Thanks,
ruth ruth.jonkman@wcom.com

 

[PaulF]

I recommend making a new table with the same structure as tblSystems, call this one tblTemp. Add 24 records to tblTemp and put 1 through 24 in the num# field. Then make a new subform (sub2) which uses tblTemp as the Recordsource. Set the Visible property for sub2 to false. When you click on the command button to add new system data, set the visible property for the subform showing the data from tblSystems to false and set the visible property for sub2 to true. Add a new command button (if one doesn't already exist) to save the new system information. When this command button is clicked, add code to run a query which appends the data from tblTemp to tblSystems, setting the criteria so that only records with data in them get appended. After that run an Update query to set all of the fields in tblTemp back to Null (except the Num# field). Then set the visible property for sub2 back to false, set the visible property for the 1st subform back to true, and refresh the form.

PaulF

 

[JerryDennison]

I STRONGLY recommend you do not populate your db with empty records. It is not a good practice, nor is it necessary. But if you ABSOLUTELY, POSITIVELY have to then there are a couple of ways to do this. Probably the easiest to implement and debug would be the UpdateQuery route. Create a table with one field, we'll call it numbers. Populate this table manually with your numbers 1 - 24. Create an update query with this table and the primary table(not the one the subform is based on but the one the mainform is based on). You will not need a join line between the two table. Place the number field from the number table and the primary key field from the mainform's table on the QBE grid. In the criteria property of the mainform's primary key field put the following substituting your actual names with the placeholders given.

Forms!MainFormName!PrimaryKey

With the mainform open and on a valid record you can test the query by switching to datasheet view. You should see 24 records. Numbered 1 through 24 for the number field and the same primary key value next to each of these. Switch back to design view and change the query to an Append query. It will ask which table to append to. Select the table the subform is based on. Then for the append to property of the number field select the Num# field and for the PrimaryKey field select the foreign key field. The query is now ready to use. Don't use it now, however. You must take precaution not to append to any dataset that already has the records. On your main form you can add a button that will run the append query. Add some code to insure you don't append records that have already been appended. One way to do this is to add something like the following:

...
If nz(DCount("[Num#]", "tblSubtableName", "[ForeignKey] = " & Me.PrimaryKey)) > 0 Then
DoCmd.CancelEvent
Exit Sub
Else
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppendQueryName"
DoCmd.SetWarning True
End If
...

 

[ruthcali]

Paul F and Jerry--thank you very much for your response! i really appreciate it.

Jerry, i understand populating a database with empty records is not good, but how should i do it so the users ONLY can enter 24 systems for each piece of equipment? if they enter more, there will be trouble! (Why did the database let me enter more, blah blah blah).

i figured, if i populate the # column for them and they get into the habit of having the records there for them, when they get to record 24, they will stop.

but, if THEY have to enter a value in the # column each time they add a system, what will stop them from adding #25 and then #26, etc.?

thanks for your advice,
ruth
ruth.jonkman@wcom.com

 

[PaulF]

ruth
My solution didn't add blank records. If you read it, it says to only append those records that contain the system data (set the criteria to the field where you enter the data, not the Num# Field). Also to solve your last question, if you use the Temp table, and set the Allow Additions to False, you should be able to limit them to only the max of 24.

PaulF

 

[JerryDennison]

If all you want to do is limit the user to 24 subrecords for any given piece of equipment you can do this without adding any more tables or "empty" records. In the OnCurrent event procedure for the subform add the following code:

...
If DCount("SubTableForeignKeyName", "SubTableName", "[MainTablePrimaryKeyName] = " & Forms!MainFormName!PrimaryKeyName) = 24 Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If
...

(be sure to substitute your actual names for the placeholders given)

 

[JerryDennison]

Paul, it took me several hours from the time I started my respoonse to when I actually posted it. Therefore, I hadn't read your response and my was not intended as a slam against it in any way. I would have recommended that "empty" records not be added no matter what. Sorry, if my answer seemed it was in response to yours it wasn't.