problem of adding two fields in a query to code vba (1 Viewer)

azhar2006

Registered User.
Local time
Today, 13:54
Joined
Feb 8, 2012
Messages
180
Brother (arnelgp) helped me in writing this code and I am very grateful to him for the help. I have added two new fields in the two tables as well as the query. But I tried a lot to add them to this code and it didn't work. Please help me, thank you very much. Your friend AZ


Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strUnifiedNumber As Long
Dim strSpecialization As Variant
Dim intCounter As Integer
Dim strSQL As String

Set db = CurrentDb
Set rs = db.OpenRecordset("qryUnifiedNumber", dbOpenSnapshot)

'Make sure we have records and then
'make sure we are at the first record
If rs.RecordCount < 1 Then
    MsgBox "No records require an update"
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
End If
'rs.MoveFirst

rs.MoveLast
rs.MoveFirst
intCounter = rs.RecordCount
MsgBox "You are about to update " & intCounter & " records."
'We need to loop through all of the records
'that our query object found
While rs.EOF = False

    strUnifiedNumber = rs![UnifiedNumber]
    strSpecialization = rs![Specialization]
    strSQL = "UPDATE Table2 SET Table2.Specialization = '" & strSpecialization & "' WHERE ((Table2.UnifiedNumber)=" & strUnifiedNumber & ")"

    db.Execute strSQL, dbFailOnError

rs.MoveNext

Wend
rs.Close
Set rs = Nothing
Set db = Nothing
MsgBox "Complete"
 

Attachments

  • arnelgp.zip
    27.8 KB · Views: 243

June7

AWF VIP
Local time
Today, 12:54
Joined
Mar 9, 2014
Messages
4,308
Add them to do what - UPDATE? What data type are these new fields? What did you try? Post attempted code.
 

azhar2006

Registered User.
Local time
Today, 13:54
Joined
Feb 8, 2012
Messages
180
Add them to do what - UPDATE? What data type are these new fields? What did you try? Post attempted code.
Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strUnifiedNumber As Long
Dim strSpecialization As Variant

Dim strSN As Variant
Dim DateSn As Variant

Dim intCounter As Integer
Dim strSQL As String

Set db = CurrentDb
Set rs = db.OpenRecordset("qryUnifiedNumber", dbOpenSnapshot)

'Make sure we have records and then
'make sure we are at the first record
If rs.RecordCount < 1 Then
    MsgBox "No records require an update"
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
End If
'rs.MoveFirst

rs.MoveLast
rs.MoveFirst
intCounter = rs.RecordCount
MsgBox "You are about to update " & intCounter & " records."
'We need to loop through all of the records
'that our query object found
While rs.EOF = False

    strUnifiedNumber = rs![UnifiedNumber]
    strSpecialization = rs![Specialization]
    strSN = rs![SN]
    strDateSn = rs![DateSn]
   
   
   
    strSQL = "UPDATE Table2 SET Table2.Specialization = '" & strSpecialization & "' WHERE ((Table2.UnifiedNumber)=" & strUnifiedNumber & ")"
    strSQL = "UPDATE Table2 SET Table2.SN = '" & strSN & "' WHERE ((Table2.UnifiedNumber)=" & strUnifiedNumber & ")"
    strSQL = "UPDATE Table2 SET Table2.DateSn = '" & strDateSn & "' WHERE ((Table2.UnifiedNumber)=" & strUnifiedNumber & ")"

    db.Execute strSQL, dbFailOnError

rs.MoveNext

Wend
rs.Close
Set rs = Nothing
Set db = Nothing
MsgBox "Complete"
Code:
SELECT DISTINCTROW Table1.UnifiedNumber, Table1.Specialization, Table1.SN, Table1.DateSn
FROM Table1 LEFT JOIN Table2 ON (Table1.DateSn = Table2.DateSn) AND (Table1.SN = Table2.SN) AND (Table1.Specialization = Table2.Specialization);
 

Attachments

  • image_2021-11-24_213549.png
    image_2021-11-24_213549.png
    44.4 KB · Views: 235

June7

AWF VIP
Local time
Today, 12:54
Joined
Mar 9, 2014
Messages
4,308
Assuming DateSn is a date/time field, use # delimiter. If any field is a number type, don't use any delimiter. UPDATE as many fields as needed in one SQL, don't repeat UPDATE and WHERE.
Code:
strSQL = "UPDATE Table2 SET Specialization = '" & strSpecialization & "', SN = '" & strSN & "' DateSn = #" & strDateSn & "# WHERE UnifiedNumber=" & strUnifiedNumber
Really should give table better name than Table2.
 

azhar2006

Registered User.
Local time
Today, 13:54
Joined
Feb 8, 2012
Messages
180
Assuming DateSn is a date/time field, use # delimiter. If any field is a number type, don't use any delimiter. UPDATE as many fields as needed in one SQL, don't repeat UPDATE and WHERE.
Code:
strSQL = "UPDATE Table2 SET Specialization = '" & strSpecialization & "', SN = '" & strSN & "' DateSn = #" & strDateSn & "# WHERE UnifiedNumber=" & strUnifiedNumber
Really should give table better name than Table2.
Thank you very much for the help. This is a simulation database only, not the original. I use the table names explicitly in it. I got this message about the date field.
 

Attachments

  • 66.JPG
    66.JPG
    20.8 KB · Views: 232

theDBguy

I’m here to help
Staff member
Local time
Today, 13:54
Joined
Oct 29, 2018
Messages
18,791
I opened your database file, and it displayed this form. I clicked on both buttons, and they worked. So, what exactly is the problem? Perhaps you can post a link to the original thread discussion to provide some background, in case it helps explain the problem.

1637782512619.png
 

azhar2006

Registered User.
Local time
Today, 13:54
Joined
Feb 8, 2012
Messages
180
I opened your database file, and it displayed this form. I clicked on both buttons, and they worked. So, what exactly is the problem? Perhaps you can post a link to the original thread discussion to provide some background, in case it helps explain the problem.

View attachment 96372
Thank you dear (theDBguy) Please see this attachment
 

Attachments

  • arnelgp.accdb
    548 KB · Views: 218

theDBguy

I’m here to help
Staff member
Local time
Today, 13:54
Joined
Oct 29, 2018
Messages
18,791
Thank you dear (theDBguy) Please see this attachment
Looks like you're missing a comma.
SQL:
strSQL = "UPDATE Table2 SET Specialization = '" & strSpecialization & "', SN = '" & strSN & "',  DateSn = #" & strDateSn & "# WHERE UnifiedNumber=" & strUnifiedNumber
 

azhar2006

Registered User.
Local time
Today, 13:54
Joined
Feb 8, 2012
Messages
180
Looks like you're missing a comma.
SQL:
strSQL = "UPDATE Table2 SET Specialization = '" & strSpecialization & "', SN = '" & strSN & "',  DateSn = #" & strDateSn & "# WHERE UnifiedNumber=" & strUnifiedNumber
It has already done the update but it clears me this error message
 

Attachments

  • 6.JPG
    6.JPG
    68.8 KB · Views: 213

Users who are viewing this thread

Top Bottom