We help IT Professionals succeed at work.

access update query using a form

I want to have a form where you have two text boxes to type in the range to be updated.  The next part is to have the fields that are in the table to be updated in my case invoice#, SO#, shipping date and PO#.  and then a command button to update the records.  I havent done this in a while and as wondering the quickest way.  thanks
Comment
Watch Question

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Your question is not clear.

Can you provide more detail please...?


JeffCoachman
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
How are the Invoice, Shipping Date, SO and PO number to be updated? Will the user enter values for those items in textboxes, and you then want to update the records in a table based on the range specified in the textboxes?

If so, you can just issue an UPDATE statement, perhaps when the user clicks an Update button:

Sub Update_Click()
  Currentdb.Execute "UPDATE YourTable SET InvoiceNum=" & Me.txInvoiceNum & ", SONum=" & me.txSONum & ", PONum=" & Me.txPONum & ", ShipDate=#" & Me.txShipDate & "# WHERE YourDateField BETWEEN #" & Me.txFirstDate & "# AND #" & Me.txSecondDate & "#"
End Sub

Author

Commented:
yes the user will enter these numbers on the form

First S/N  
Last S/N  range

Shipping info
Invoice#
SO#
Shipping Date
PO#

Update records
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Okay ... then modify the code I gave you to match the Fields in your Tables, and the controls on your Form.

Note too that if any of your Fields are TEXT, then you'll need to enclose them in quotes. For example, in InvoiceNum is a Text field:

Currentdb.Execute "UPDATE YourTable SET InvoiceNum='" & Me.txInvoiceNum & "', SONum=" & me.txSONum & ", PONum=" & Me.txPONum & ", ShipDate=#" & Me.txShipDate & "# WHERE YourDateField BETWEEN #" & Me.txFirstDate & "# AND #" & Me.txSecondDate & "#"

In the statement above, items like "InvoiceNum", "SONum", etc are the FIELDS in your table. Items like "Me.txInvoiceNum", "Me.txSONum", etc are CONTROLS on your form. Modify the statement accordingly.

Author

Commented:
Currentdb.Execute "UPDATE TBLRVC SET Invoice#='" & Me.txtInvoiceNum & "', SONO=" & me.txtSONum & ", PO#=" & Me.txtPONum & ", ShippingDate=#" & Me.txtShipDate & "# WHERE [shippingdate] BETWEEN #" & Me.txtFirstDate & "# AND #" & Me.txtSecondDate & "#"

this is what I have, On the form they are unbound and then under the name I have the me.txtshipdate, etc.  

Author

Commented:
it doesnt seem to like something its highlighting the me.txtinvoicenum

Author

Commented:
method or data member not found

Author

Commented:
boag2000:Your question is not clear.

Can you provide more detail please...?


JeffCoachman

Jeff,

I want to have a form where a user inputs two dates as a range.  Then type in the PONo, SONo, ShippingDate, and PackingSlipNo.  Then the user will click an update button where it will update to the table and then be ready for reports.  hope that helps
Right now I have unbound columns for each of these on a form and just didnt know what was the best way to go about it.
thanks
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Do you have a control named "txtInvoiceNumber" on your form? If you don't you'll have to change that code to reflect the controls and such on your form.

In essence the code is "looking" for the controls on the form - for example, you're basically telling that SQL statement to set the value of Invoice# to whatever value it finds in txtInvoiceNum. If there is no control named txtInvoiceNum, then the statement will fail.

Also note that your Field names are non-standard, and therefore should be enclosed in square brackets ( [ ] ). For example:

Currentdb.Execute "UPDATE TBLRVC SET [Invoice#]='" & Me.txtInvoiceNum & "', [SONO]=" & me.txtSONum & ", [PO#]=" & Me.txtPONum & ", [ShippingDate]=#" & Me.txtShipDate & "# WHERE [shippingdate] BETWEEN #" & Me.txtFirstDate & "# AND #" & Me.txtSecondDate & "#"



Author

Commented:
txtInvoiceNum is the name of my first text box (the control is blank right, its unbound) and it has a bracket around it

Author

Commented:
This is what my code looks like and now there is a syntax error

TBLRVC fields are called PackingSlipNo, SONo, PONo, ShippingDate
My form is called test
My text boxes on the form are called txInvoiceNum, txSONum, txShipDate, txPONum

Private Sub Command42_Click()
CurrentDb.Execute "UPDATE TBLRVC SET [PackingSlipNo]='" & Me.txInvoiceNum & "', [SONO]=" & Me.txSONum & ", [PONo]=" & Me.txPONum & ", [ShippingDate]=#" & Me.txShipDate & "# WHERE [shippingdate] BETWEEN #" & Me.txFirstDate & "# AND #" & Me.txSecondDate & "#"

End Sub
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
As I said in an earlier comment:

If any of those fields are TEXT fields, you'll have to insure that they are surrounded by single or double quotes, like your PackingSlipNo does.

In Access, Text values must be enclosed in single or Double quotes. Date values must be enclosed by hash marks ( # ). Numeric values are not enclosed with anything.

So if SONO is a Text field, you must enclose the value of txSONum in quotes, as I did with the value of PackingSlipNo. Same with PONo.

Author

Commented:
there are all text fields in the table and the date is a date of course

Author

Commented:
ok i added the single quote to the others and the new error is syntax error in date in query expression '#'
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Try taking the # out from around the criteria:

CurrentDb.Execute "UPDATE TBLRVC SET [PackingSlipNo]='" & Me.txInvoiceNum & "', [SONO]=" & Me.txSONum & ", [PONo]=" & Me.txPONum & ", [ShippingDate]=#" & Me.txShipDate & "# WHERE [shippingdate] BETWEEN " & Me.txFirstDate & " AND " & Me.txSecondDate

Author

Commented:
It then says im missing syntax operators when taking those out

Author

Commented:
I dont know what Im doing wrong....

unbound text box called txfirstdate
unbound text box called txlastdate
this is the range to be looked at when the person puts the dates in

Once those dates are in  then there are four  unbound fields that the user types data into.  They have the names said above. Then a command button that triggers the code above to update the tblrvc.  

The table should update the four fields for everything between those dates

Author

Commented:
I created a database to show you my form and my table and the code.  
Database5.accdb
Hamed NasrRetired IT Professional

Commented:
Update query:
UPDATE tblrvc SET tblrvc.PONo = [forms]![test]![txPONum], tblrvc.SONo = [forms]![test]![txSONum], tblrvc.ShippingDate = [forms]![test]![txShipDate], tblrvc.PackingSlipNo = [forms]![test]![txInvoiceNum], tblrvc.[Date] = [forms]![test]![txShipDate]
WHERE (((tblrvc.Date) Between [forms]![test]![txFirstDate] And [forms]![test]![txSecondDate]));

Check the database.

Database5-2.accdb