Populating a Form with info from a Table

[smcthatsme]

I am not an expert in Access, so please reply as plainly as possible with
step by step instructions. Thanks!

Question: I want to upload Customer information from a Table into a Form
simply by selecting the Customer Name and having the rest of the fields
populate with the corresponding data (for use in taking phone orders). How
can I do this?

 

[Jeff Boyce]

Create a query (in design view) that returns all of the records from that
table. Add a selection criterion to the CustomerID field that looks
something like:

Forms!YourNewForm!cboYourSelectedCustomer

Create a query that returns CustomerID, CustomerName.

Create a form in design view. Make the first query the source, and add
controls to display the fields.

Add one more control, a combobox (usually the upper right corner of the form
header is a place most folks look when the form opens). Make the second
query the source for this combobox, which you might name
"cboYourSelectedCustomer". Make the first column the bound column
(CustomerID), set it's width to 0, and make the second column wide enough to
see the names.

In that combobox's AfterUpdate event, add:

Me.Requery

Here's how this combination works:
* When you first open the form, it depend on the query, which looks in
the combobox, sees nothing, and returns that record (i.e., nothing).
* When you finish selecting a name in the combobox, the AfterUpdate event
fires the Requery, which looks in the combobox for a selected CustomerID,
sees one, and gets THAT customer's record and displays it.

Is that what you were after?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mavr]

Hello. I'm from Russia so I have to apologise beforehand for my poor
grammatics and using incorrect words.

I have a similar problem too, but it's rather difficult: in my main table
there are many records in different fields that belongs to the same
organisation (i.e., multiple orders of different sorts of goods made by the
same organisation in the same day; each order has it's own quantity of goods,
own price and so on). Structure of main table is: "Code" - key field;
"OrderDate" – time/date field; "OrganisationName" – text field (combobox);
"Order№" – number field; "SortOfGoods" – text filed (combobox); "Quantity" –
text field; "PriceForUnit" – text field. Combobox fields get their values
from appropriate table's key field. Jack's method shows only ONE order's data
simultaneously, but I need ALL fields that are concerned to the same
organisation to be shown. It supposed to be something like a list-field, but
I wonder what should I do...

Unfortunately, there is another thing to do – I need a field "Month" in
form, after chosing certain month from which, a list of all orders made by
all organisations in this month appears on screen (i.e. all records, relaying
to this month, are shown). Here I have to say that field "Date" in main table
is in short time format (dd.mm.yyyy), it complicates my task (a bit?).

 

[Jeff Boyce]

If describing this in Russian would work better, consider posting a new
thread and asking "Alex Dybenko" if he's available in the subject line.

Regards

Jeff B.

 

[smcthatsme]

Thanks for your reply. Unfortunately I couldn't get it to work using your
instructions. I'm afraid I'm a bit confused by your instructions:
"Create a form in design view. Make the first query the source, and add

controls to display the fields."

What do you mean by "add controls to display the fields."? I have the form
prepared with all the names of the info it needs to match the info in the
table. However when I used your instructions it would only return the info
for the 1st record in the table. If I chose another customer from the drop
down list, the info for the first customer stayed in the form (address, city,
state, zip, tank info...etc).

I wish I had a live person to walk me through this. I just don't understand
Sql speak. It's like reading German to me!

 

[Jeff Boyce]

If you are not familiar with adding controls to a form in design view, you
may need more generic assistance than the volunteers here can usually
provide. Consider contacting a local community college to see if they might
have a student working with Access who could talk with you.

If you add a control to a form, and base the form on a
recordset/query/table, you can "bind" a control to one of the fields.
That's what I'm referring to...

Any chance any of your underlying tables are using a "lookup" data type
field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mavr]

Umgh... I doubt that it would, but I've already done this and Alex didn't
help me, arguing that it is typical situation described in all Access
textbooks. I've studied some (3, if more precise) of these textbooks but
found nothing. Sorry If my explanation of a problem wasn't clear at all, but
would you be so kind to help me? Please...

"Jeff Boyce" пишет:

 

[Jeff Boyce]

I didn't see Alex' response, but I can imagine...

Your earlier description sounded more like what one might find in a
spreadsheet. If you want to get good use of Access' relationally-oriented
features/functions, you cannot feed it 'sheet data.

If you are looking for a "tell me which button to push" solution, don't be
surprised (or offended) if the folks who volunteer their time here in the
newsgroups suggest that you might need to reconsider how your data is
organized before moving on to working with it.

"How" depends on "what"...

Good luck! (and feel free to continue posting ... if this approach is
taking too long, consider hiring someone ...)

Regards

Jeff Boyce
Microsoft Office/Access MVP