Code help from AllenBrowne not working

Access11

Registered User.
Local time
Yesterday, 23:13
Joined
Nov 21, 2012
Messages
15
Hello all, Happy Holidays

I used a code from Allen Browne website to pass value from Form and Subform to a new table, but it does not work for me. Can someone take a look at the code and see what is wrong? I get a Compile error: Method or data member not found on my WHERE clause, at least that where it is highlighted. Not sure how to run the Debug.Print on this code.

Private Sub cmdSaveRevision_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save any edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
![BOM No] = Me.BOM_No
![Version Code] = Me.VersionCode
![BOM Description] = Me.[BOM Description]
![Unit of Measure Code] = Me.[Unit of Measure Code]
![Status] = Me.Status
![Revision Date] = Me.txtRevisionDate
![Created by] = Me.txt_CreatedBy
![Last Modified by] = Me.txt_LastModifiedBy
![Last Date Modified] = Me.txt_LastDateModified
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = ![BOM No] And [Version Code]

'Duplicate the related records: append query.

If Me.sftblTempMaster.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tblMaster2 (*) '" & _
"SELECT " & lngID & " As NewID, Type, No, [Variant Code], Description, Quantity, Scrap % '" & _
"FROM tblMaster2 WHERE [BOM No]= '" & Me.BOM_No & " ' AND [Version Code]= '" & Me.Version_Code & "';"

DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdSaveRevision_Click"
Resume Exit_Handler
End Sub
 
Oh, and you don't want the single quotes around the SELECT clause.
 
Thank you for the tip I checked and all fields with spaces have []. Still not sure why the code is not working, any thoughts?
 
They don't above. Did you use the debugging technique? What's the SQL?
 
Maybe I'm missing something. I just caught the Scrap % and made the change , anything else you see? MY VBA and SQL are subpar at best. I appreciate your help on this. My SQL is

strSql = "INSERT INTO tblMaster2 (*) '" & _
"SELECT " & lngID & " As NewID, Type, No, [Variant Code], Description, Quantity, [Scrap %] '" & _
"FROM tblMaster2 WHERE [BOM No]= '" & Me.BOM_No & " ' AND [Version Code]= '" & Me.Version_Code & "';"
 
The single quotes are still around the whole statement, and I'm looking for the finished SQL available with the debugging technique I posted.
 
Sorry, I missed the post about the Single Quotes. I thought I had to use them as some fields such as Type, Varaint Code are text fields. Let me try and run the SQL Debug and see if I can do it. Sorry foir my lack of knowledge on these matters.
 
You need them around text values, but not around the entire statement.
 
Okay I ran the SQL and obviously I did it wrong cause notiing came back. I did notice that when I got the error thought the very beginning of my Code was hightlighted in yellow. the Private Sub cmdSaveRevision_Click() part. Again sorry, I'm sure I'm testing your nerves now

If Me.sftblTempMaster.Form.RecordsetClone.RecordCount > 0 Then
Debug.Print strSql
strSql = "INSERT INTO tblMaster2 (*) '" & _
"SELECT " & lngID & " As NewID, Type, No, [Variant Code], Description, Quantity, [Scrap %] " & _
"FROM tblMaster2 WHERE [BOM No]= '" & Me.BOM_No & "' AND [Version Code]= '" & Me.Version_Code & "';"

DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If
 
The debug line has to be after the string is built. Can you post the db here?
 
I cut down the DB to show just what can be seen.

Basically the DB works this way. You select a BOM No (Build of Material) it populates on another form that has a subform. One BOM can have multiple No (Items) attached to it. when it opens user should be able to make revision and save to a exisitng table (tblMaster2). But the allen browne code is not working for me. Let me know how bad i screwed the code up. Thanks again for the help.

Edit: I can't use AutoNumber as a PK cause of the multiple items associated with the BOM No, or at least I haven't figured that part out yet.
 

Attachments

Last edited:
About to head out to dinner with my wife, so it will likely be tomorrow before I get back to this.
 
Enjoy your dinner and thank you for the time and effort you have put into this. I appreciate it.
 
Dinner turned into Thanksgiving holiday plus weekend trip to see family. :p Did you get this sorted out?
 
That's how Thanksgiving should be. Hope you enjoyed it.

Unfortunately no I have not been able to sort this out. I tried all weekend to make any change that would work. I wish there was an easier way , where I wouldn't need SQL to run this code. Hopefully you can look at the DB and make sense out of it. It really puts it in perspective how much I still have to learn.
 
What I could see that you need some rework on your field names at least. I think if you put some time in renaming, it would ultimately help.

As PBaldy suggested, I also think you should use a standard naming convention all through the dB.

BTW, your BOM NO field appears with different names BOM NO, BOMNo, ...
 
Thank you, Yes I have reworked it a little to be more conventional.
 
These lines worked some how but you need to change field names of BOM No and version code.

strSql = "INSERT INTO tblMaster2 (*) " & _
"SELECT " & lngID & " As NewID, 'Type', 'No', '[Variant Code]', 'Description', Quantity, [Scrap %]" & _
"FROM tblMaster2 WHERE [BOM No]= " & Me.BOM_No & " AND [Version Code]= " & Me.Version_Code & ";"

As PBaldy suggested, you have to remove the ' quotation marks from some places.

best regards.
 
I changed all the names to show as followd BOMNo, VersionCode,

strSql = "INSERT INTO tblMaster2 (*) '" & _
"SELECT " & lngID & " As NewID, Type, No, VariantCode, Description, Quantity, Scrap% " & _
"FROM tblMaster2 WHERE BOMNo= '" & Me.BOM_No & "' AND VersionCode= '" & Me.Version_Code & "';"


What's sad is I don;t even know how to check to see if it is right. pbaldy told me to use debug.Print, but I failed on that as well.

Do you see where I can make changes?
 
Last edited:

Users who are viewing this thread

Back
Top Bottom