Auto-Fill Text Boxes in A Form From a Table 4

[lunchboxkid]

Hello,
I'm new to the forum so any help is already appreciated!! I have a form that has three seperate text boxes (Item No., Catalog No., Description). I also have an inventory table with all the data for the form to pull necessary data from. My question is when I type in the item number in the first text box is it possible for the catalog number and description text boxes to auto-populate with the information that is associated with that item number from the table? Thanks again for any assistance!!

 

[LonnieJohnson]

If these three text boxes are not bound to your table you can use a DLookup function in your Item No text box's After Update Event...

Private Sub ItemNoTextBox_AfterUpdate()
CatelogNoTextBox = DLookup("CatelogNo", "InvTable", _
"ItemNo = '" & ItemNoTextBox & "'&quot
DoCmd.Close acTable, "InvTable"
DescriptionTextBox = DLookup("Description", "InvTable", _
"ItemNo = '" & ItemNoTextBox & "'&quot
End Sub

note: InvTable = the name of your inventory table.

Hope this helps. B-)
ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[matpj]

I have a similar problem.

would this same principal work for an external (Oracle) table?

do I need to create text-boxes within the design view of the Form, rather than drag the fields from the Table list?

I have four main feilds that lie in the Oracle Table, Event Description, Event ID, Start Date, End Date.

I want to have the Event Description as a combo box, which will then fill in the rest of the fields.
how would I go about doing this? do I create the combo-box and use DLookup for the rest of the fields?

help me...!!

 

[LonnieJohnson]

Yes matpj,

If your table is linked to your Access database you can use this same procedure in the ComboBox's After update event. Add the fields from the tool box and do not bound them to the fields in the table.

Good luck... B-) ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[matpj]

how do you choose to have the feilds unbound?

So as long as the table is linked to the database I do not need a query?
how do I display one of the fields from the Oracle table as a combo-box?
I just want the one screen, so the user doesn't have to cycle through all the records to find the event they are interested in!

 

[mrfilez]

I have two questions.
1. Would this work with list boxes? The info that I want to populate the other fields may be numerous.

2. Is it possible to use this same format to pull from multiple tables? A couple of fields from one table, a couple of fields from another table. Thank you for your help and time. It is greatly appreciated!!!

Thanks,
Don
please CC both addresses
f613493c@mailfxhome.fedex.com
mrfilez@midsouth.rr.com

 

[LonnieJohnson]

MATPJ,

1. An unbound textbox is one that is not linked to a field in the form's record source.
2. True, if the table is linked you don't need to run a query. However if you have other fields that you want to show on the form, and this information is selected by criteria other than what will be in the DLookup funtion, then you will need a query or the table as a record source to the form.
3. To use one of the fields from the table as your list in the combo box you will need to go to the properties window for the combobox and select the Data Tab. Here you go to the Row Type property and choose Table/Query. Then go to the Row Source property and hit the elispe button (the one with three dots) and you will get a query grid. Now pick your table from the list then pick your field. Be sure the query property Unique Value is set to Yes so that your list will not have duplicates.

mrfiles,

1. Yes this will work for listboxes as well. You may want to put it in the click or doubleclick event.
2. Yes, you can populate each field from a different table as long as they have the value of your list box in the table as well.

Hope I am on the right track... B-) ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[mrfilez]

I just realized I am not populating from a table. I am populating from a query. Basically what I have is this.

Common Name
Order
Family
Scientific Name
Seen
Date
Place

All of these could have multiple listings. Could see the same animal in multiple places on multiple dates. I want to search for say, Order. I would like for it to show me all animals in the Order with the info from the other fields.
I tried using DLookup and Me!fieldname = Me![Fieldname].column(#).
With the second way I was using the columns from the query that is the source for the Order field on the form.

I have managed to get it to show me the first animal in an Order the way I wanted, but I would like for it to show me a list of all the animals.

This is basically the last component for the DB to be functional.

Thank you for your help and time. It is greatly appreciated!!!

Thanks,
Don
please CC both addresses
f613493c@mailfxhome.fedex.com
mrfilez@midsouth.rr.com

 

[LonnieJohnson]

mrfilez,

What about this:
1. Build a form that only has a list showing all the entries for Order.
2. Create a query that will get your records and for the Order Field's criteria, use the form's listbox:

Forms![FromName]![ListBoxName]

3. Create a subform on your main form and use the query as the record source.

4. Now in the listbox's click event, requery the subform:

SubFormName.Requery

Let me know if this is more what you need. ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[lunchboxkid]

LonnieJohnson,

I'm still having issues with the original post in this thread. I used the code provided and it has not performed. It may be that I did not convey that my Item Number text box was bound to another table that records data entry records from my form. So what I need is the fields to draw info from the InvTable and still add data entry records to my original table through my form. Does this make sense?

Also Please check my code I may have made an error since I'm new to VB...

Private Sub Item_NumberTextBox_AfterUpdate()
Catalog_NumberTextBox = DLookup("Catalog_Number", "InvTable",
"Item_Number = '" & Item_NumberTextBox & "'&quot
DoCmd.Close acTable, "InvTable"
DescriptionTextBox = DLookup("Description", "InvTable", _
"Item_Number = '" & Item_NumberTextBox & "'&quot
End Sub

Appreciate your expertise!

john.heeley@home.com

 

[LonnieJohnson]

OK.

1. Put the control source back to the ItemNumber field in your data entry table.

2. Place the above code in the form's On Current event and the ItemNumber field's AfterUpdate event.

3. As far as your code is concerned, you may be getting a "Data type mismatch error..." if your ItemNumber field is a numeric field. Remove the tick marks inside the quotes like so:

Private Sub Item_NumberTextBox_AfterUpdate()
Catalog_NumberTextBox = DLookup("Catalog_Number", "InvTable",
"Item_Number = " & Item_NumberTextBox )
DoCmd.Close acTable, "InvTable"
DescriptionTextBox = DLookup("Description", "InvTable", _
"Item_Number = " & Item_NumberTextBox )
End Sub

We only need the tick marks for strings expressions. I should have included that.

Let me know... B-)
ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[matpj]

Lonnie,

I hate to be a pain, and drag this up again. I am having problems.
can I tell you the names of my fields and table, so that you may apply your code (above) to my problem
I'll give you two of the fields so I start to understand how it works.

I have a table: EV200_EVENT_MASTER
On my form there are two fields:
Description: combo-box(unbound)taking data from EV200_EVENT_DESC

ID: Unbound text box. (needs data from EV200_EVENT_ID)

I need to get the user to pick a value from the Description field, which will then fill out the relevant ID (EV200_EVENT_ID)

could you show me where to pu the names/fields in your code.
This way I can apply it to more fields when I add them!

I assume that nothing needs to be put in the properties for the text box? Is it just the combo-box that needs event procedures?

thank you very much for your help,
Matthew

 

[lunchboxkid]

Thanks for your help Lonnie! The original post in this thread works great as long as you don't forget to set the primary key on your item number in your table! (As I did!) Many thanks to you! Now that my wheels are turning my next step is to allow item number additions (NotInList) if they are not found in this table utilizing a message box to ask if they are sure they want to continue, then allowing them to add the new item number without it being added to the original item file table that the Dlookup uses. Any thoughts?

LBK john.heeley@home.com

 

[LonnieJohnson]

LBK,

You can control how or if an item gets added to the combo box list in the On Not In List event of the combo box. There are three constants for the Response setting. (See MS Access tutorial for "Not In List Event".

Not exactly sure what you are asking. If this is not the case please explain in more detail.

ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[matpj]

Lonnie,

I hate to be a pain, and drag this up again. I am having problems.
can I tell you the names of my fields and table, so that you may apply your code (above) to my problem
I'll give you two of the fields so I start to understand how it works.

I have a table: EV200_EVENT_MASTER
On my form there are two fields:
Description: combo-box(unbound)taking data from EV200_EVENT_DESC

ID: Unbound text box. (needs data from EV200_EVENT_ID)

I need to get the user to pick a value from the Description field, which will then fill out the relevant ID (EV200_EVENT_ID)

could you show me where to pu the names/fields in your code.
This way I can apply it to more fields when I add them!

I assume that nothing needs to be put in the properties for the text box? Is it just the combo-box that needs event procedures?

thank you very much for your help,
Matthew

 

[LonnieJohnson]

MATPJ,

Looking closer at your situation. You may be better off going a different route.

1. For the Row Source use this SQL statement:

SELECT DISTINCT EV200_EVENT_DESC, EV200_EVENT_ID FROM
EV200_EVENT_MASTER ORDER BY EV200_EVENT_DESC

(This will allow you to pull each description with it's corresponding id number into the combo box)

2. Set your combo box's Column Count property to 2.
This will allow both columns to part of your drop down list.

3. Set the Column Width property to 1;0 . This makes the first column (your descriptions) 1 inch wide and the second column 0 inches wide (invisible to the user).

4. Finally in the Combo Box's After Update Event we put:


Private Sub EV200_EVENT_DESC_After_Update ()
EV200_EVENT_ID = EV200_EVENT_DESC.Column(1)
End Sub

This procedure says to take what is in the second column (the id number) and puts it in the ID field.

Note the Column property is a zero based thing so that is why column 2 is denoted by EV200_EVENT_DESC.Column(1).

Let me know how this works.

B-) ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[matpj]

Lonnie, thanks for your reply,


when I try and select a record from the EV200_EVENT_DESC, it returns an error:

Runtime Error: .... 'object required'

my unbound text box is labelled 'ID' but called 'Text4', and I have tried both these in the AfterUpdate feild:

e.g.:

Private Sub EV200_EVENT_DESC_After_Update ()
Text4= EV200_EVENT_DESC.Column(1)
End Sub

this does not seem to work though
can you suggest why?

 

[matpj]

Lonnie,

I have sorted it out.
The reason was, that the field in my form that gets data from EV200_EVENT_DESC is called 'Description'. I put that in the AfterUpdate, instead of 'EV200_EVENT_DESC' and it works fine.

thankyou very much!

 

[djnick]

Hello...

I can just about use access to create a database and some simple forms.. so please make sure all answers are nice and clear. thank you..

the problem I have got, is a simple one. I using access as a members database each member should have a unique number.

I want advice / help / code on the following :

I want to be able to go to the membersid field and type in a number.

after I have entered that number I would like to see if it allready exists and warns / shows the details of that user in some form.

if not after completing the record and a new record is being to created to take the number of the last member and add 1 to this.

is this possible ?
is this possible for me to do ? (lol)

please as much info as poss. dont assume anything.. also any good book on starting to write proper database where it looks more like a program than access would also be gratelly recieved.

 

[Jean01]

Lonnie,
The solution that you provided to Lunchboxkid works fine; however, I would like to add 2 other features:
1- I would like to add extra information like: ItemDimention and ItemColor to the records. These fields would appear on the form only when clicking on something in the form, otherwise they are not be shown.
2- If no matching ItemNo found, the user would fill-in the remaining fields and the new record would be added to the InvTable.
Any ideas?
thanks in advance.
I forgot to mention that I'm using Access97