UPDATING RECORDS

[HerickP]

Hi, its me again <br><br>Thanks for your help, My database seems fine, but as DougP said &quot; when u get one problem fixed, another million appear&quot;...<br><br>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 &quot;Date Arrived&quot; will be blank since the order has not arrived, and field &quot;Status&quot; 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 &quot;Date Arrived&quot; and &quot;Status&quot; to the delivered date and status will be now &quot;DELIVERED&quot;. 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 guys!!!!

 

[rbowes]

I would create a form that is based on a parameterized Select query that includes the three fields you want - the form should be set to be a pop-up.&nbsp;&nbsp;Create a header and with an unbound textbox call it txtPONumber. The parameter should be set to the unbound control Forms![frmMyForm]![txtPONumber].&nbsp;&nbsp;This will make the detail sectio of the form blank.&nbsp;&nbsp;In the detail section of the form set the PO field to Enabled = No; Locked = Yes. This will make the DateArrived field have the focus.<br><br>on the AfterUpdate event (you could also place a command button to do this) put in this procedure:<br>Dim db as dao.database<br>dim rec as dao.recordset<br>dim intRecords as integer<br>dim strSQL as string<br><br>strSQL = Your select statement<br><br>set db = currentdb()<br>Set rec = db.openrecordset(strSQL)<br><br>intrecords = rec.recordCount<br><br>If intRecords = 0 Then<br>&nbsp;&nbsp;MsgBox &quot;Sorry that is not valid PO Number&quot;<br>&nbsp;&nbsp;Exit sub<br>Else<br>&nbsp;&nbsp;Me.Requery<br>End if<br><br>On the form, go to the DateArrived field on the AfterUpdate event place this procedure:<br>Me.Status = &quot;DELIVERED&quot;<br><br>place a command button in the form footer cmdSave.&nbsp;&nbsp;On the click event place this event procedure:<br>DoCmd.RunCommand acCmdSaveRecord<br>Me.txtPONumber = &quot;&quot;<br>Me.requery<br>Me.txtPONUmber.SetFocus<br><br><br>That should do it. Let me know if it works<br><br>Bob B.<br><br>

 

[JimConrad]

Constructive criticism... <br><br>Sounds like you need a part-time analyst/programmer to help you create a complete system. <br><br>I've worked with folks in the past who tried to develop systems on their own.&nbsp;&nbsp;They ran into problems (which had major impacts on their business) because they just didn't have the experience needed to understand all the issues involved.<br><br>I'm not trying to discourage you from creating a system in Access (which I think is the greatest thing since sliced bread and canned beer), but there are pitfalls along the way and a few dollars spent on getting help now, may save many thousands later... <p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>

 

[HerickP]

Just some questions:<br><br><br>The parameter

 

[HerickP]

Imcomplete Post<br><br>The parameter &quot;Forms![frmMyForm]![txtPONumber]&quot; Goes where?? and FrmMyForm should be my forms name???<br><br>The Code:&nbsp;&nbsp;<br><br>Dim db as dao.database<br>dim rec as dao.recordset<br>dim intRecords as integer<br>dim strSQL as string<br><br>strSQL = Your select statement<br><br>set db = currentdb()<br>Set rec = db.openrecordset(strSQL)<br><br>intrecords = rec.recordCount<br><br>If intRecords = 0 Then<br>&nbsp;&nbsp;MsgBox &quot;Sorry that is not valid PO Number&quot;<br>&nbsp;&nbsp;Exit sub<br>Else<br>&nbsp;&nbsp;Me.Requery<br>End if<br><br>Goes on whos AfterUpdate procedure???<br><br>Thanks a lot!!!<br><br>

 

[HerickP]

Hi RBowes,<br><br>Can you help me figuring out some things? I dunno where to put the code &quot;Forms![frmMyForm]![txtPONumber]&quot;. U said its a parameter, but since I am newbie in access, I have no idea where this goes. and FrmMyForm should be the forms name I used? Also, the big code upthere, u said it goes on the AfterUpdate procedure, but I dunno for what object, the form???? and last,there is a line in the code:<br><br>strSQL = Your select statement<br><br>I dunno whats my slect statement. I have created the query with the 3 fields I needed, but I dunno what goes here. I apprecciate so much your help time, and sorry for bothering by posting again, cause I really dunno how to do it.<br><br>Thanks so much!!!!&nbsp;&nbsp;

 

[rbowes]

Sorry about that.&nbsp;&nbsp;Instead of strSQL = Your select statement, you can use your save query object as easily as a SQL statement. Open the query and in the Criteria row under PO Number place &quot;Forms![frmMyForm]![txtPONumber]&quot;(No quotes).&nbsp;&nbsp;frmMyForm is a generic name of the form that you will be using to update your data. <br><br>Back on the your form: Click the control txtPONumber and open the properties form.&nbsp;&nbsp;Go to events and click the After Update event, now click the &quot;...&quot; elipse button and select code builder<br><br>The Code:&nbsp;&nbsp;<br><br>Sub txtPONumber_AfterUpdate()<br>Dim db as dao.database<br>dim rec as dao.recordset<br>dim intRecords as integer<br><br>set db = currentdb()<br>Set rec = db.openrecordset(&quot;yourQuery&quot<br><br>intrecords = rec.recordCount<br><br>If intRecords = 0 Then<br>&nbsp;&nbsp;MsgBox &quot;Sorry that is not valid PO Number&quot;<br>&nbsp;&nbsp;Exit sub<br>Else<br>&nbsp;&nbsp;Me.Requery<br>End if<br><br>End Sub<br>PS: a Select SQL statement is easy to write in vba<br>I would write your statement like this: (and by the way, I forgot something in the first response that would have made this easier to work in vba)<br>(this assumes that the table is called tblOrders)<br>Dim strSQL as string<br>Dim strParameter as string<br>strSQL = &quot;SELECT tblOrders.[PO Number], <br>tblOrders.[Date Arrived], tblOrders.[Status]&quot;<br>strSQL = strSQL & &quot; FROM tblOrders&quot;<br>strSQL = strSQL & &quot; WHERE tblOrders.[PO Number] =&quot; & strParameter<br><br>Best place to learn this is the SQL View of any of your queries.<br><br>Hope that helps clarify things.<br><br>Bob