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
jpb12345Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Your question is not clear.

Can you provide more detail please...?


JeffCoachman
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jpb12345Author 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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
jpb12345Author 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.  

jpb12345Author Commented:
it doesnt seem to like something its highlighting the me.txtinvoicenum
jpb12345Author Commented:
method or data member not found
jpb12345Author 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 (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 & "#"



jpb12345Author Commented:
txtInvoiceNum is the name of my first text box (the control is blank right, its unbound) and it has a bracket around it
jpb12345Author 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 (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
jpb12345Author Commented:
there are all text fields in the table and the date is a date of course
jpb12345Author Commented:
ok i added the single quote to the others and the new error is syntax error in date in query expression '#'
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
jpb12345Author Commented:
It then says im missing syntax operators when taking those out
jpb12345Author 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
jpb12345Author Commented:
I created a database to show you my form and my table and the code.  
Database5.accdb
Hamed NasrRetired IT ProfessionalCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.