Copy subform data to next new record.
- Thread starter zoey1210
- Start date
Thank You, Thank You, Thank You VilaRestal!!
This was the only code that worked for me (my ACCESS db was connected to mysql tables on an online server).
What I am trying to do now is copy multiple related records based on check boxes on a form....(from other tables). I've repeated this line "CurrentDb.Execute.." to copy multiple related records (in other tables) and it works accept if the table has no records, I get an error that says "object not found".
What I would like to do is have a form with a series of check boxes to give the user options on which related records to copy. Do you know a good way to do this?
I tried this, but it doesn't seem to work:
I would like to cancel the statement then go onto the next if the check box is false (and if there are no records in the table to copy). As you can see I'm a little wet behind the ears when it comes to this advanced stuff! If you could help me that would be great!
This was the only code that worked for me (my ACCESS db was connected to mysql tables on an online server).
What I am trying to do now is copy multiple related records based on check boxes on a form....(from other tables). I've repeated this line "CurrentDb.Execute.." to copy multiple related records (in other tables) and it works accept if the table has no records, I get an error that says "object not found".
What I would like to do is have a form with a series of check boxes to give the user options on which related records to copy. Do you know a good way to do this?
I tried this, but it doesn't seem to work:
Code:
If Forms!frmCopy!GeneralInfoCheck = True Then
CurrentDb.Execute "INSERT INTO tblGeneralInfo (CompanyID, LabelID, Field) SELECT " & iNewID & ", LabelID, Field FROM tblGeneralInfo WHERE CompanyID = " & Me.CompanyID
Else: 'do nothing
If Forms!frmCopy!AvailabilityCheck = True Then
CurrentDb.Execute "INSERT INTO tblCountries (CompanyID, Countries) SELECT " & iNewID & ", Countries FROM tblCountries WHERE CompanyID = " & Me.CompanyID
Else: 'do nothing
VilaRestal
';drop database master;--
- Local time
- Today, 16:12
- Joined
- Jun 8, 2011
- Messages
- 1,046
As far as having checkboxes for records that the user can select records to be copied is concerned:
You're hitting on a bit of a limitation of Access - I'm expecting you mean a continuous form or datasheet with checkboxes on each record. The checkboxes can't be bound to a calculated field because then they wouldn't be updateable and if they're not bound then they can't be associated to the record so there's only one checkbox that can only have one value: tick one ticks them all.
The way I see it there are two ways to do it:
Temporary Table Method
Base the subform on a temporary table that will have a boolean field that the checkbox will bind to. You're going to want to create the table and populate it and bind the subform to it using VBA in the parent form's current event. And dispose of the table when you're done with it.
(You could try and do it with a field added to a non-temporary table but then you've got issues with multiple users interfering with each other's selections.)
Listview Method
Use a listview instead of a subform and have the checkbox as a column in that. You'll need to populate it on the current event.
I think the Listview way is best (I would try to avoid creating temporary tables wherever possible) but they're not the most straightforward thing to use. Also, the temporary table method would have the advantage of being able to do the insert in a single SQL statement ("Insert... Select From the temporary table Where checkbox is ticked" (SQL pseudocode there)) whereas the list view would have to be done in a VBA loop (For Each item in listview If item.checkboxcolumn ticked Then CurrentDb.Execute "Insert Values"...).
But everything else about the listview will be easier.
As for your code it seems ok except the unnecessary elses and no end ifs:
Oh and having a field named Field is perhaps a little bit risky (might conflict with reserved words) and definitely very cryptic
I'm not sure where iNewID comes from. I trust that's assigned properly beforehand and CompanyID isn't an identity field.
It's often a good idea to set the string to a variable and debug.print that before executing it. Then you can copy the string into a query design in SQL view and see whether it works there (and if not why not).
I hope that helps.
You're hitting on a bit of a limitation of Access - I'm expecting you mean a continuous form or datasheet with checkboxes on each record. The checkboxes can't be bound to a calculated field because then they wouldn't be updateable and if they're not bound then they can't be associated to the record so there's only one checkbox that can only have one value: tick one ticks them all.
The way I see it there are two ways to do it:
Temporary Table Method
Base the subform on a temporary table that will have a boolean field that the checkbox will bind to. You're going to want to create the table and populate it and bind the subform to it using VBA in the parent form's current event. And dispose of the table when you're done with it.
(You could try and do it with a field added to a non-temporary table but then you've got issues with multiple users interfering with each other's selections.)
Listview Method
Use a listview instead of a subform and have the checkbox as a column in that. You'll need to populate it on the current event.
I think the Listview way is best (I would try to avoid creating temporary tables wherever possible) but they're not the most straightforward thing to use. Also, the temporary table method would have the advantage of being able to do the insert in a single SQL statement ("Insert... Select From the temporary table Where checkbox is ticked" (SQL pseudocode there)) whereas the list view would have to be done in a VBA loop (For Each item in listview If item.checkboxcolumn ticked Then CurrentDb.Execute "Insert Values"...).
But everything else about the listview will be easier.
As for your code it seems ok except the unnecessary elses and no end ifs:
Code:
If Forms!frmCopy!GeneralInfoCheck = True Then
CurrentDb.Execute "INSERT INTO tblGeneralInfo (CompanyID, LabelID, Field) SELECT " & iNewID & ", LabelID, Field FROM tblGeneralInfo WHERE CompanyID = " & Me.CompanyID
End If
If Forms!frmCopy!AvailabilityCheck = True Then
CurrentDb.Execute "INSERT INTO tblCountries (CompanyID, Countries) SELECT " & iNewID & ", Countries FROM tblCountries WHERE CompanyID = " & Me.CompanyID
End IfOh and having a field named Field is perhaps a little bit risky (might conflict with reserved words) and definitely very cryptic
I'm not sure where iNewID comes from. I trust that's assigned properly beforehand and CompanyID isn't an identity field.
It's often a good idea to set the string to a variable and debug.print that before executing it. Then you can copy the string into a query design in SQL view and see whether it works there (and if not why not).
Code:
Dim sSQL As String
If Forms!frmCopy!GeneralInfoCheck = True Then
sSQL = "INSERT INTO tblGeneralInfo (CompanyID, LabelID, Field) SELECT " & iNewID & ", LabelID, Field FROM tblGeneralInfo WHERE CompanyID = " & Me.CompanyID
Debug.Print sSQL
CurrentDb.Execute sSQL
End If
If Forms!frmCopy!AvailabilityCheck = True Then
sSQL = "INSERT INTO tblCountries (CompanyID, Countries) SELECT " & iNewID & ", Countries FROM tblCountries WHERE CompanyID = " & Me.CompanyID
Debug.Print sSQL
CurrentDb.Execute sSQL
End IfI hope that helps.
