I have a form that I use to enter data into a table. Fields are:<br><br>PO Number<br>Customer Name<br>Date Shipped<br>Date Arrived<br>Status<br><br>Everytime an order gets shipped, I will add a record to the table using the form, but Field "Date Arrived" will be blank since the order has not arrived, and field "Status" will be IN TRANSIT, because the shipment is on the way to the customers home. Now, everytime the order gets to the customer, I will have to update the fields "Date Arrived" and "Status" to the delivered date and status will be now "DELIVERED". Now, how can I , or whats the easier way to have this fields updated? I know I could just go in to the table and update each one, but since, for example, PO numbers are unique, could I have a find feature, some form where I could type the PO and have it come up with the fields to update or something really easy to have these fields updated??? Thanks Once again!!!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
UPDATE FIELDS EASILY
- Thread starter HerickP
- Start date
1. Create a search button on your PO form.<br>In design view, in the toolbar make sure you have the function wizard turned on (this will make it real easy). click on the Button button on the toolbar and follow the steps to make a search button on your form. -if you want to always search by PO, after the wizard is done buiding the button, go into the On Click property of the button and put this code before the first line of the code that is already there:<br><b>Me.PO_Number.SetFocus</b><br><br>2. Updating both at once<br>you may want to put an After Update procedure in for Date Arrived that looks like this (put it in through Code Builder once again):<br><b>Status = "DELIVERED"</b><br>you could also put an OnClick procedure for Date Arrived of <b>Date()</b>, which will automatically fill in the current date for you<br><br> <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>
- Thread starter
- #3
in desing view of the form. simply make sure you have the "Wizard" button checked on your ToolBox toolbar, and then click on the "Button" button on your Toolbox toolbar. <br>(go to View, and make sure the Toolbox toolbar option is checked first). <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>
- Thread starter
- #5
Well, thats not the way I wanted, and it seems the only solution , but Thanks a lot.<br><br>No other way like, I could have a form where they would type the PO in a texbox, then this would check if the PO is actually in the table, if no there, a msg box could comeup saying "PO invalid", so u would ahve to try again. If in the table, then u could add "DELIVERED" to delivery status text box, and enter the date delivered in the other textbox, and then hit a command button "to save", and so on...Just wondering if it could be done...
ok, new form, create a field named "PoSearch"<br>on the AfterUpdate property of that field put this statement, changing any names that are not right for your application:<br><b><br> Dim stDocName As String<br> Dim stLinkCriteria As String<br><br> stDocName = "YourFormName"<br> <br> stLinkCriteria = "[PO Number]=" & Me![POSearch]<br> DoCmd.OpenForm stDocName, , , stLinkCriteria<br> DoCmd.Close acDefault,,acSaveYes<br></b><br><br>open to the record if it exists.<br> <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>
Try this one..
Create a Command Button and paste this code in the on-click action.
_________________________________
Private Sub cmdFind_Click()
On Error GoTo Err_cmdFind_Click
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_cmdFind_Click:
Exit Sub
Err_cmdFind_Click:
MsgBox Err.Description
Resume Exit_cmdFind_Click
End Sub
___________________________________
This will allow you to search for text in whatever field the cursor was previously in.
Hope this helps.
Phil
Create a Command Button and paste this code in the on-click action.
_________________________________
Private Sub cmdFind_Click()
On Error GoTo Err_cmdFind_Click
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_cmdFind_Click:
Exit Sub
Err_cmdFind_Click:
MsgBox Err.Description
Resume Exit_cmdFind_Click
End Sub
___________________________________
This will allow you to search for text in whatever field the cursor was previously in.
Hope this helps.
Phil
BlackKnight
Programmer
Hi,
A good way would be to use an update query to update the Date Arrived and status fields with the PO #s as criteria.
Now, since you want to get the po numbers you want to update easily on a form. Create an unbound list box call it lstPoNums on a form with its rowsource set to show the po numbers. Make sure the bound column is set to the Po Number.
Set its row source to this query
SELECT [PO Number] FROM MyTable GROUP BY [PO Number] ORDER BY [PO Number]
Have a command button called Update. In the command button's ON Click event
dim strWhere as string, strSql as string
strWhere = ""
strSQl = "UPDATE MyTable SET [Date Arrived] = Date(), [Status] = "DELIVERED "
strWhere = WHERE [Po Number]='" & lstPoNums & "'"
strSQL= strSQL & strWhere
docmd.runsql strSQL
It will update the selected PO for those two fields. Sicne you are using a listbox ther is no need to ensure the PO exists.
I can't remember if the listbox has a multiselect property but if it does you can then allow all of the po numbers to be selected at once. If so you would have to use a loop to get all of the selected items and build the where clause.
Have a good one!
BK
A good way would be to use an update query to update the Date Arrived and status fields with the PO #s as criteria.
Now, since you want to get the po numbers you want to update easily on a form. Create an unbound list box call it lstPoNums on a form with its rowsource set to show the po numbers. Make sure the bound column is set to the Po Number.
Set its row source to this query
SELECT [PO Number] FROM MyTable GROUP BY [PO Number] ORDER BY [PO Number]
Have a command button called Update. In the command button's ON Click event
dim strWhere as string, strSql as string
strWhere = ""
strSQl = "UPDATE MyTable SET [Date Arrived] = Date(), [Status] = "DELIVERED "
strWhere = WHERE [Po Number]='" & lstPoNums & "'"
strSQL= strSQL & strWhere
docmd.runsql strSQL
It will update the selected PO for those two fields. Sicne you are using a listbox ther is no need to ensure the PO exists.
I can't remember if the listbox has a multiselect property but if it does you can then allow all of the po numbers to be selected at once. If so you would have to use a loop to get all of the selected items and build the where clause.
Have a good one!
BK
- Status
Similar threads
- Locked
- Question
- Locked
- Question
