access update query using a form

Your question is not clear.

Can you provide more detail please...?


JeffCoachman

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

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.

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.  

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

method or data member not found

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

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 & "#"

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

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

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.

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

ok i added the single quote to the others and the new error is syntax error in date in query expression '#'

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

It then says im missing syntax operators when taking those out

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

I created a database to show you my form and my table and the code.  
Database5.accdb

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