Update Query 1

[DrillMonkey]

Hi forum,

I have a multi-user database (2) users. My fields are | InvoiceId | Date | Driver | School | PurchaseOrder | Requisition | Ticket | Packages | Description | Signature | Memo. The drivers create multiple records then print an invoice. At the end of the day, or the next day he edits the signature field with the person who signed the invoice. I need an update routine that automates this procedure. I was thinking of a parameter update based on the “date, driver, and school” I am a novice at best so simple code edits would be helpful.
I am open to all suggestions.
Thanks
Richard

 

[dhookom]

What field do you want to update? If it is the signature field, aren't all signatures unique? I think you need to provide some sample records and a real live example.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DrillMonkey]

Yes the signature field. The update query should test 4 conditions.

1. [Date] What date are you searching for?
2. [School] what school?
3. Which name to update the [signature] field?
4. check to see if the [signature] field is "null" if so apply the update

the first 3 are user input, does this make sence?

 

[dhookom]

Assuming the user is current on a record with just updated signature, you could use code like:

Dim strSQL as String
strSQL = "UPDATE tblNoNameGiven " & _
   "SET Signature = """ & Me.txtSignature & _
   """ WHERE Signature is Null AND [Date]=#"
 & _
   Me.txtDate & "# AND [School]=""" & Me.txtSchool & _
   """ AND Driver=" & Me.txtDriver 
DoCmd.RunSQL strSQL

This makes assumptions about your control names and data types.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DrillMonkey]

Duane,

Will this code produce a message box for user input, or do I need to setup controls on my form?
Thanks

 

[dhookom]

Controls on forms would be a good idea. If we knew the names of the controls, the names of fields and tables, and the event you wanted to trigger the update, someone might be able to provide a less generic answer.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DrillMonkey]

It works, but I do have one small gilch a "Enter Parameter Value" box pops up just before the update this has to do with the driver field.

Private Sub cmdSearch_Click()
Dim strSQL As String
strSQL = "UPDATE Items " & _
   "SET Signature = """ & Me.txtSignature & _
   """ WHERE Signature is Null AND [Date]=#" & _
   Me.txtDate & "# AND [School]=""" & Me.txtSchool & _
   """ AND Driver =" & Me.txtDriver
DoCmd.RunSQL strSQL
End Sub

 

[dhookom]

Is Driver a text or numeric field? Do you understand the difference between using text, numbers, and dates in code? You may need to treat Driver like you did Signature with extra quote marks.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DrillMonkey]

My driver field is text, if I add more quote marks it goes right to the debug window. I assume you mean like this

Driver="""

 

[PHV]

strSQL = "UPDATE Items " & _
   "SET Signature = '" & Me.txtSignature & _
   "' WHERE Signature is Null AND [Date]=#" & _
   Me.txtDate & "# AND [School]="' & Me.txtSchool & _
   "' AND Driver='" & Me.txtDriver & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

Small correction to transpose a single and double quote:

strSQL = "UPDATE Items " & _
   "SET Signature = '" & Me.txtSignature & _
   "' WHERE Signature is Null AND [Date]=#" & _
   Me.txtDate & "# AND [School]='" & Me.txtSchool & _
   "' AND Driver='" & Me.txtDriver & "'"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Good catch Duane !

 

[DrillMonkey]

YES very good catch! works perfectly thanks for your time Duane..

 

[DrillMonkey]

Also thanks to you PHV...