SQL string help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In a previous post I was looking for a solution to this, and I think I found
what I need, but I need help with writing the code to execute an SQL
statement.....

Here is what I want to accomplish:

In the table 'tblVehicles' I have the following fields

VehicleID, VehicleNumber, SeviceDueDate, ServiceDueKms, CurrentDisposition,
CurrentLocation and VehicleDescription

In the table 'tblDispositions' I have the following fields

DispositionNumber, AuthorizedBy, DispositionDate, Disposition,
DispositionReason, DispositionNotes, VehicleLocation, VehicleNumber

A form 'frmEnterDisposition' allows the user to add a new disposition.

From the form 'frmEnterDisposition' I want to take the value of the field
'Disposition' and enter this value into the 'CurrentDisposition' field of the
table 'tblVehicles' and specifically into the row that matches the
VehicleNumber from the field "VehicleNumber'



So, I think what I can do is use an SQL string such as:


Update tblVehicles
Set CurrentDisposition = Me.Disposition
Where tblVehicles.VehicleNumber = Me.VehicleNumber


What do I need to do to accomplish this???


Cheers
 
not sure if i got this right (as far as understanding your question) but you
can add a command button to the form with the following code:

docmd.runsql Update tblVehicles Set CurrentDisposition = Me.Disposition
Where tblVehicles.VehicleNumber = Me.VehicleNumber
 
I think you have the concept right, however, the format doesn't work. I get a
compile error : Expected: list separator or )

I am using Acces 97.

Cheers
 
Hi,
If your SQL statement is accurate, all you have to do is:

Dim strSql as String

strSql = "Update tblVehicles Set CurrentDisposition = '" & Me.Disposition & _
"' Where tblVehicles.VehicleNumber = " & Me.VehicleNumber
CurrentDb.Execute strSql, dbFailOnError

I assumed that CurrentDisposition is a string, if it's numerical, remove the single quote
delimiters.
 
Back
Top