Autofill form from text box

[araman]

I have a multiple item form that displays fields from an Orders table based on a date range. I want to be able to fill the Received field for the displayed records from a text box on the form all in one shot. Is that possible

I am new at access so please keep that in mind when answering


Thanks,
Lance

[Minion]

If I am understanding your correctly you need to basically move the value of one text box to several other text boxes on your form am I right? If this is the case there is a couple ways you can do this and each is built on basically the same model.

You could:

• Use a button that would copy the value on command.
  Have the value copy to the boxes once the orginal box is done being edited.
  Or in the case the basis for the Received time is calculated by the form it can be added to the other boxes when the form loads or changes.

As I'm not sure exactly what method will work best for you I will add a basic example of the code. If you are not familar with VBA let us know and we should be able to help.

Private Event()
   With Me
      .TextBox1 = .ReceivedDate
      .TextBox2 = .ReceivedDate
      .TextBox3 = .ReceivedDate
      .TextBox4 = .ReceivedDate
   End with
End Sub
 

Hope this helps or at least gets you moving in the right direction.

- Minion -

[araman]

Thanks for the reply. I am not familiar with coding.

I have one text box that i want to fill one datasheet subform field which is "Received" from the orders table which is based on a query by date

Thanks,

[jaxjagfan]

Are these 2 different tables you are updating? Do you have an Orders table and an OrderItems table and trying to flag all items as being "Received" for a particular order?

Without knowing your table/application structure, this is a way to update multiple records.
Attach this to a command button.

Docmd.RunSQL "Update OrderItems Set Received ='" & Me.txtReceived & "' Where OrderID=" & Me.txtOrderID & ";"

Once the correct order is selected in the main form (OrderID) then you have to update the "Received" field in the OrderItems table.

You will need to be more specific with tables, forms and database structure to get a more precise answer.

Hope this helps!

[araman]

I am sure I'm not explaining myself correctly so I'll try by describing what I want to do instead.

i have a table called Orders. In that table resides a field called Received. What I would like to be able to do is populate the Received field to a date of my choosing based on a start date-end date (order date is also a field) criteria and Received is null on a simple click of a command button. Then I could manually delete any Item manually that was backordered.

Thanks,

Lance

[jaxjagfan]

I am sure I'm not explaining myself correctly so I'll try by describing what I want to do instead.

i have a table called Orders. In that table resides a field called Received. What I would like to be able to do is populate the Received field to a date of my choosing based on a start date-end date (order date is also a field) criteria and Received is null on a simple click of a command button. Then I could manually delete any Item manually that was backordered.

Thanks,

Lance

Do you want to update all orders between a chosen start date and end date? Or just a particular order? It makes a difference whether you will be updating all orders within a date range or just one particular order.

In your data structure can an order have more than one item type type per order?

FYI - If you have an ItemStatus field (Ordered, Rcvd, BackOrdered, etc) then you could add the deletion of the backordered items to the same command button.

[araman]

Thanks for helping.

update by date range and if received (which is a date) value is null

Right now I have an access 2007 Multiple Item form which is wizarded off a query with only the Orders table with the criteria- Between [Start Date] and [End Date] , Received is null.
The Form shows all items matching this. I would like to attach a command button and a text box on this form in which i enter the date i would like to date these records to be received to a date i put in the text box, updating the Orders table only for those records that were returned by the query.

[PianoMan64]

update by date range and if received (which is a date) value is null

Right now I have an access 2007 Multiple Item form which is wizarded off a query with only the Orders table with the criteria- Between [Start Date] and [End Date] , Received is null.
The Form shows all items matching this. I would like to attach a command button and a text box on this form in which i enter the date i would like to date these records to be received to a date i put in the text box, updating the Orders table only for those records that were returned by the query.

I think I know what you want to do. You want to be able to put a Start and End Date into two different fields and then click Update Orders and the date that you want to put into that is the current date that you pushed the button?

If so here is what you're going to need to do:

First create the two Text Control boxes (Those are the ones that allow you to put something into a field. Please make sure that the wizard function on the form is turned off, otherwise it's going to be asking you questions about what you want to do with each field and the button on the form.

Please name the first TextControl txtStartDate.
Please name the Second TextControl txtEndDate
Please name the Button Control btnUpdateOrders

www.joepottschmidt.com/example1.htm

Then copy and paste this code into your Subroutine that is for the Button Click event.

 
Private Sub btnUpdateOrders_Click()
 
'-----------------------------------------------------------------------------------------------------------------
'Copy from the top of this line down to just before the End Sub
'-----------------------------------------------------------------------------------------------------------------
          Dim MyDB as DAO.Database
          Dim MyRS as DAO.Recordset
 
          Set MyDB = CurrentDB()
          Set MyRS = MyDB.OpenRecordset("SELECT * FROM [Table Name] " & _
                "WHERE OrderDate = #" & me.txtStartDate & "# AND #" & me.txtEndDate & "#", dbOpenDynaset)
 
          With MyRS
                  Do While Not .EOF
                       .Edit
                       !TheNameOfTheDateFieldThatYouWantToChange = me.TxtTheDate 
                       'This is the field that you want to update as well as the 
'                       field on the form that you want to enter the date of these orders.
                       .Update
                       .MoveNext
                  Loop
          End With
 
MyRS.Close
MyDB.Close
 
Set MyRS = nothing
Set MyDB = Nothing
'-------------------------------------------------------------------------------------------------------------------
'Stop Copying Here
--------------------------------------------------------------------------------------------------------------------
 
End Sub
 
 

Hope that helps,

Joe P.

[araman]

thanks for the reply

this is what i got that works, it look much like yours

Private Sub Command22_Click()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
Do Until rs.EOF
rs.Edit
rs!Received = Me.Text20
rs.Update
rs.MoveNext
Loop


End Sub


Thanks,

Lance