By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

Autofill form from text box

P: 53
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
Jan 8 '08 #1
Share this Question
Share on Google+
8 Replies


Minion
Expert 100+
P: 108
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.

Expand|Select|Wrap|Line Numbers
  1. Private Event()
  2.    With Me
  3.       .TextBox1 = .ReceivedDate
  4.       .TextBox2 = .ReceivedDate
  5.       .TextBox3 = .ReceivedDate
  6.       .TextBox4 = .ReceivedDate
  7.    End with
  8. End Sub
  9.  
Hope this helps or at least gets you moving in the right direction.

- Minion -
Jan 8 '08 #2

P: 53
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,
Jan 8 '08 #3

jaxjagfan
Expert 100+
P: 254
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!
Jan 8 '08 #4

P: 53
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
Jan 8 '08 #5

jaxjagfan
Expert 100+
P: 254
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.
Jan 8 '08 #6

P: 53
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.
Jan 8 '08 #7

Expert 100+
P: 374

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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub btnUpdateOrders_Click()
  3.  
  4. '-----------------------------------------------------------------------------------------------------------------
  5. 'Copy from the top of this line down to just before the End Sub
  6. '-----------------------------------------------------------------------------------------------------------------
  7.           Dim MyDB as DAO.Database
  8.           Dim MyRS as DAO.Recordset
  9.  
  10.           Set MyDB = CurrentDB()
  11.           Set MyRS = MyDB.OpenRecordset("SELECT * FROM [Table Name] " & _
  12.                 "WHERE OrderDate = #" & me.txtStartDate & "# AND #" & me.txtEndDate & "#", dbOpenDynaset)
  13.  
  14.           With MyRS
  15.                   Do While Not .EOF
  16.                        .Edit
  17.                        !TheNameOfTheDateFieldThatYouWantToChange = me.TxtTheDate 
  18.                        'This is the field that you want to update as well as the 
  19. '                       field on the form that you want to enter the date of these orders.
  20.                        .Update
  21.                        .MoveNext
  22.                   Loop
  23.           End With
  24.  
  25. MyRS.Close
  26. MyDB.Close
  27.  
  28. Set MyRS = nothing
  29. Set MyDB = Nothing
  30. '-------------------------------------------------------------------------------------------------------------------
  31. 'Stop Copying Here
  32. --------------------------------------------------------------------------------------------------------------------
  33.  
  34. End Sub
  35.  
  36.  
Hope that helps,

Joe P.
Jan 10 '08 #8

P: 53
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
Jan 10 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.