how do I autopopulate a form field from a table (1 Viewer)

[Wayne Jordan]

Hello everyone, this is my first post here.
I am fairly new to Access and it is my only programming experience. I don't know VBA and have used very little SQL.
I am in charge of consumable supplies in our warehouse and have built a database to record inventory, create reports for orders etc.

I have a table with all of the basic information for each item we use which I have named tlbItems. It includes the following fields:
ItemID - The part number which also serves as the primary key
OrderQty - The quantity ordered at one time
OrderPrice - The price of the item

I have a form (frmOrders) that I use to create a report to send to our purchasing department when I need to order something. On this form is a combo box attached to the field ItemID in tblItems. I choose the item in that combo box then enter the desired quantity in a text box ItemQty. I want to change it so that upon exiting the combo box ItemId, the text box ItemQty automatically populates with the data from the field OrderQty in tblItems and the same thing for a second text box Price.

I want to go to the properties for the text box ItemID on the form and build an event procedure using the wizard. Can anyone help, please?

Thank you

 

[pbaldy]

One way

http://www.baldyweb.com/Autofill.htm

 

[Wayne Jordan]

OK, I am very confused. pbaldy, I tried what you sent in the link above and had mixed results. Here is more detailed information.

I have tbl.Items with fields ItemId, Item, VendorID, VendorName, VendorAddress, VendorCity, VendorState, VendorZip, OrdQty, and OrdPrice. OrdQty is the normal order qty for that item and OrdPrice is the price quoted for that quantity. ItemId is the part number and Item is the name of the part. (ex. 007-2013, 3” tape)

tblOrdDetails with fields ItemId, OrdQty, OrdPrice. The reason I have the same fields in both tables is because the quantity may change due to certain reasons which could affect the price and I would have to enter the order quantity and order price manually using another form. Therefore, I need to store the quantity and price for each particular order which will not always equal what is in tblItems, which does not violate normalization as I understand it. I have a query that appends tblOrdDetails with this orders information. That form and query are working properly.

I am creating a form with comboItemID with columns ItemID, Item, OrdQty and OrdPrice in that order and text boxes txtOrdQty and txtOrdPrice also on this form. comboItemID has as row source a select query Select ItemID, Item, OrdQty, OrdPrice FROM Itms; . I want to select the ItemId in the combo box and fill the textboxes with the OrdQty and OrdPrice to make the job quicker without having to manually look up the normal order qty and price for that item. In txtOrdQty the control source is =comboItemID.column(3). This text box fills properly with the data desired. In txtOrdPrice the control source is =comboItemID.column(4), but it does not work. I have checked a dozen times the syntax, field names etc. and all are correct. I can’t figure out why one is working and the other is not. I am pulling out what little hair I have left.

To make matters even more confusing, I have on the manual form text boxes txtVendorID, txtVendorName, txtVendorAddress, txtVendorCity, txtVendorState and txtVendorZip. I enter the Vendor Id in txtVendorID and when the cursor leaves that text box the other text boxes fill with their respective information. I built this database several years ago, moved to another position and then returned to this one. I do not remember how I did it. I thought I would simply go to the text boxes and see how then copy that technique. I cannot find anything in the properties of those text boxes!??? I looked in txtVendorId expecting to see a query or event procedure under the Event tab at On Lost Focus, but nothing is there. For txtVendorName I show control source VendorName, which confuses me because I expected something like tblItems.VendorName.

This is my first database and it is a clumsy beast. I recognize that. I want to throw my computer against the wall but I am pretty sure I would lose my job.

 

[pbaldy]

You realize the column property is zero based? Since you only have 4 columns in comboItemID, you'd want 2 and 3 as the columns, not 3 and 4.

 

[Wayne Jordan]

Yes, I do. I did not list all the fields of the query in my post. That was sloppy. The columns are numbered correctly, and the text box associated with column (3) fills properly. The box for column (4) does not.
Thank you for taking the time to work with me on this.

 

[pbaldy]

Then make sure the column count property of the combo is correct.

 

[Wayne Jordan]

I am sorry that I forgot to thank you earlier.

 

[pbaldy]

No worries, I hope you got it sorted out.

 

[David R]

If I ever get lost on column(x), I use the Immediate window in VBA to double check myself.

 

[Wayne Jordan]

It was the column count property.
Thanks Dave but the most I know about VBA is that the VB stands for Visual Basic. I don't even know what the A stands for. I have barely dabbled in SQL. So far I have been able to do basically everything without them. I do want to learn.

Yeah, it may come with MS Office but it is much more involved than Excel. I have learned that the hard way but have also learned much more and am very glad I have done so.

Thanks all.

 

[David R]

No worries, it takes a little more trial and error to do it without Visual Basic for Applications.

For instance, you could temporarily put a field on a form that has its source set to Me.blah.column(x) ... or you could just learn how to count better than I.