Access VBA - lookup and alter records problems

[griff84]

G'Day Guys im a newbie to access.

I'm trying to implement a new database at work.
I've tried for hours looking through technical sites and forums and can't find a similar problem already posted.

What Im trying to achieve is, as the end user is filling out a form ie.[Build Checklist] it will update records in other tables in the database. The example in the psudocode posted below is trying to match a record from the database to the input data in the form after the form updates it then alters existing records to bring them up to date

Private Sub Server_Serial_AfterUpdate()
loop through [Hardware Assets] records
 
If [Hardware Assets]![SerialNumber] = [Build Checklist]![GWSerialNumber] Then
    [Hardware Assets]![LastHandledBy] = [Build Checklist]![GWBuildBy]
 
End If
end loop
 

If someone could give me some ideas as to the sort of functions I should be using to search and alter the record with VBA or any other method would be appreciated

Thanks

Griff84

[Lysander]

G'Day Guys im a newbie to access.

I'm trying to implement a new database at work.
I've tried for hours looking through technical sites and forums and can't find a similar problem already posted.

What Im trying to achieve is, as the end user is filling out a form ie.[Build Checklist] it will update records in other tables in the database. The example in the psudocode posted below is trying to match a record from the database to the input data in the form after the form updates it then alters existing records to bring them up to date

Private Sub Server_Serial_AfterUpdate()
loop through [Hardware Assets] records
 
If [Hardware Assets]![SerialNumber] = [Build Checklist]![GWSerialNumber] Then
    [Hardware Assets]![LastHandledBy] = [Build Checklist]![GWBuildBy]
 
End If
end loop
 

If someone could give me some ideas as to the sort of functions I should be using to search and alter the record with VBA or any other method would be appreciated

Thanks

Griff84

Not sure exactly what you want to do, but here are some ideas.

Base your form (build checklist) on a query linking 'Hardware Assets' to 'Build Checklist' tables and have lasthandledby as a hidden control on the form. Then in code, as the serial number is updated, change lasthandledby.

If 'Build Checklist' is not a table, or you can't link it to 'Hardware Assets' then in the after_update event of either the control or the form, you can run something like this.

docmd.runsql "UPDATE [Hardware Assets] SET [LastHandledBy] = '" & GWBuildBy & "' WHERE SerialNumber=" & GWSerialNumber & ";"

This assumes GWSerialNumber and GWBuildBy are controls on the form and that GWBuildBy is a string, the other a number.

[griff84]

docmd.runsql "UPDATE [Hardware Assets] SET [LastHandledBy] = '" & GWBuildBy & "' WHERE SerialNumber=" & GWSerialNumber & ";"

It took a little fiddling but the above code worked like a charm sorry for the late reply I only work here once a week

[Lysander]

It took a little fiddling but the above code worked like a charm sorry for the late reply I only work here once a week

Glad it work, pleased to be of help