problem of adding two fields in a query to code vba

[azhar2006]

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

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"

 

[June7]

Add them to do what - UPDATE? What data type are these new fields? What did you try? Post attempted code.

 

[azhar2006]

Add them to do what - UPDATE? What data type are these new fields? What did you try? Post attempted 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"

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);

 

[June7]

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.

strSQL = "UPDATE Table2 SET Specialization = '" & strSpecialization & "', SN = '" & strSN & "' DateSn = #" & strDateSn & "# WHERE UnifiedNumber=" & strUnifiedNumber

Really should give table better name than Table2.

 

[azhar2006]

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.

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.

 

[theDBguy]

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.

 

[azhar2006]

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

 

[theDBguy]

Thank you dear (theDBguy) Please see this attachment

Looks like you're missing a comma.

strSQL = "UPDATE Table2 SET Specialization = '" & strSpecialization & "', SN = '" & strSN & "',  DateSn = #" & strDateSn & "# WHERE UnifiedNumber=" & strUnifiedNumber

 

[azhar2006]

Looks like you're missing a comma.

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

 

[theDBguy]

It has already done the update but it clears me this error message

That's because rs![DateSn] is empty. If you open your query, you'll see some missing dates.

 

[azhar2006]

That's because rs![DateSn] is empty. If you open your query, you'll see some missing dates.

I'm going to write this line

On Error Resume Next

again hahahah , thank you very much my friends (June7) and (theDBguy ) and (arnelgp)

 

[theDBguy]

I'm going to write this line

On Error Resume Next

again hahahah , thank you very much my friends (June7) and (theDBguy ) and (arnelgp)

Good luck!