Combo box to populate textboxes

[TrekMietta]

Hello,

I have a combobox named"sku1" and 3 textboxes named "item1", "qty1" and "cost1".

The combo box allows me to select the correct product code from the list, i then need it to get that products information from a table called "products" and populate the three text boxes with this information.

There will also be a combo box named "sku2" with three text boxes and i'm guessing you can work out the rest of the pattern, however it only goes up to "sku8".

I am guessing that this would need to be in the afterupdate event procedure, all i need is the code to do this, if someone could help me with the code for the sku1 i could hopefully adjust the code myself fot 2,3...8 etc.

The text/combo's are located on a form called Orders.

Please help.

Regards
Trek

 

[Nangasaur]

Have you tried looking at the "Customer Service" template downloadable from microsoft?

In it, there's a form called "Customer Details", with a combobox labeled "cboGoToCustomer". By looking at it's code, I think you should be able to figure this out.

edit: I forgot that it's in macro format. I converted the macro to code myself when I was playing with it a few weeks ago:

Private Sub cboGoToCustomer_AfterUpdate()

Dim ActiveControlValue As Integer
If IsNull([Screen].[ActiveControl]) Then
    Exit Sub
 
    If Me.Dirty = True Then
 
        Me.Dirty = False
 
    End If
 
End If
ActiveControlValue = ([Screen].[ActiveControl])
[Screen].[ActiveControl] = Null
If Me.FilterOn = True Then
    Me.FilterOn = False
 
End If
DoCmd.SearchForRecord , , acFirst, "[ID]=" & ActiveControlValue
End Sub

and

Private Sub cboGoToCustomer_GotFocus()
If [Screen].ActiveForm.Name = Form.Name Then
    DoCmd.Requery [Screen].ActiveControl.Name
 
    Else
 
    Exit Sub
 
End If
 
End Sub

 

[TrekMietta]

Okay i'll give it a try, i'm not very good with access, but theres no time like the present learn!

Thanks
Trek

 

[Nangasaur]

Okay i'll give it a try, i'm not very good with access, but theres no time like the present learn!

Thanks
Trek

Hey, neither am I. I've done all my learning by looking at templates, how they work, and playing with it from there. A few Google searches later, and some questions asked here, I've slowly learned a bit here and there.

Good luck!

 

[TrekMietta]

Okay, i have just looked at the code you posted, and i'm totally confused, also i dont need it to get the "qty1" value.

Here's a little more information which might help:
The form i'm using is called Orders, i need it to get information from a table called products, i only need it to get me the value for "item" and "cost" and place this information in the relevant boxes i.e.::

if i select the product code "PDL-250303" using the combobox "sku1" it will go to the product table get the "item" and the "cost" for this item and place them in the "item1" and "cost1" on the Orders form. I hope i am making this clear enough. sorry if i cause any confusion, but i have no programming experience.

Regards
Trek

 

[Nangasaur]

Okay, i have just looked at the code you posted, and i'm totally confused, also i dont need it to get the "qty1" value.

Here's a little more information which might help:
The form i'm using is called Orders, i need it to get information from a table called products, i only need it to get me the value for "item" and "cost" and place this information in the relevant boxes i.e.::

if i select the product code "PDL-250303" it will go to the product table get the "item" and the "cost" for this item and place them in the "item1" and "cost1" on the Orders form. I hope i am making this clear enough. sorry if i cause any confusion, but i have no programming experience.

Regards
Trek

Makes perfect sense.

First, you need a query, based on the table "Products". Let's call it "ProductsQuery"

Your recordsource of your form "Orders" needs to be "ProductsQuery"

On your form Orders, you'll have a textbox labeled "txtItem" and and textbox labeled "txtCost".

The control source of txtItem will be "Item". It's not grabbing this from your Table, since your Form's control source is "ProductsQuery".

The control source of txtCost will be "Cost". As above, it's grabbing this from your query, NOT your table.

The combo box I showed you is pulling up records from your table via the Query. You also need to look very closely at the properties of the combo box I showed you.

The combo box has nothing as the Control Source, but the Row Source is an SQL statement, or an embedded query:

SELECT [ProductsQuery].ID, [ProductsQuery].ProductCode
FROM [ProductsQuery]
WHERE ((([ProductsQuery].ID)<>Nz([Form]![ID],0)))
ORDER BY [ProductsQuery].[ProductCode];

Bound column of your combobox = 1
Limit to list = yes
allow value list edits = yes
inherit value list = yes
show only row source values = no
enabled = yes
column widths = 0";2"
Column heads = yes
Column count = 2


Play with it, experiment, and toy around. You'll get it.

When you use the combo box I described above, with the config settings I gave you, you'll be able to select a product with the combo box, and once selected, the form will populate the two textboxes (txtItem and txtCost) accordingly.

 

[TrekMietta]

Just to check before i try this, as i think i understand what you were saying but....

my orders form is link to the table orders where it also hold other information like shipping address and order numbers, dates etc...

currently the forms recordsource is "orders", if i change this won't it cause problems getting data from the orders table???

(I hope i'm making sense here, i dont want to sound like an idiot)
And thank you for you help!

Regards
Trek

 

[Nangasaur]

Just to check before i try this, as i think i understand what you were saying but....

my orders form is link to the table orders where it also hold other information like shipping address and order numbers, dates etc...

currently the forms recordsource is "orders", if i change this won't it cause problems getting data from the orders table???

(I hope i'm making sense here, i dont want to sound like an idiot)
And thank you for you help!

Regards
Trek

You're still making sense to me But I'm about to make little to no sense to you, so hold on to your lugnuts!

I was in the same position as you just a couple months ago, when I received the advice of "Bob" from these forums, to NORMALIZE my tables. Google "MS Access"+Normalization, and research this. It basically means to separate different types of data into separate tables. In your case, this would mean:

A table for Product Code, Cost, And Quantity (I'm assuming Quantity means cost per quantity, as in, it costs 1 dollar per 10 of an item).

A Table for shipping addresses with other customer contact information.

A Table for All your order numbers.

It's basically up to the database designer to classify WHAT types of information should be grouped together in a single table, but googling normalization (or normalization for the non-yanks!), will give you a good idea of how to normalize properly.

After normalizing your tables, you then create relationships to link the tables together. Again, a difficult process to figure out HOW to appropriately link the data, but I think the "Customer Service" template is, again, a good place to start, or even the "Inventory" template from Microsoft would be even closer to matching your needs.

Once the relationships are set, you create a query that will bring the data all together and link it properly in a form when you call up each individual record set.

Another bit of advice I found very helpful was to not think of Access as a "Spreadsheet on crack"; advice I found on this forum. After normalizing my database, setting up the relationships, and bringing the data all together with queries, I started to understand what this means - and I'm still beginning to understand even more of it every day.

You really don't want to be interracting with the data in your tables by directly linking your forms to your tables. Use a query.

 

[TrekMietta]

Okay let me explain:

i have three tables: customers, products and orders, the reason for this is that a customer always has the same billing address, the reason i have address info on the orders form is that they can haev there items sent to a different address, like there work address, or to a family members, so usually the customers shipping address is different for every order they make, but the billing address always stays the same. so teh customers main information is already held in a different table, but information relating to that specific order is held on the order table with the order information, again i hope that makes sense. so i have normalized already i just need to get the information from "products" to "orders" but still be able to i have included a screent shot that may make this more clear for you, and hopefully you will be able to help.

Also i do not need the qty information as again i made a mistake and this is different on each order and will be inputted when taking the order depending on how many they want, as you will see in the screenshot i already have the formulas to work out the costs working, i just wanted to make it easier to enter the product info instead of having to type it in each time.

I really hope you can help
Kind Regards
Trek

 

[Nangasaur]

I still highly suggest your form is linked to your data via a query, and not directly to the table itself.

I see in your screenshot that you have all your customer data in the top portion of your form, and in the bottom, you have all your product data and order info below. If you have your relationships setup properly (One Customer-to-many-products, Many Products-to-many-orders, One Customer-to-many-orders, PK set to Customer.ID, with Customer.ID linked to a FK in your products table and orders table).

I've done something similiar, and am using a subform for all my controls on the bottom of the form (as it relates to what I see in your screenshot).

Your subform would then have a query linking your products and orders together, which automatically links together due to the relationships and PK/FK links.

Slap that subform on your main form "Orders", with your "Orders" form pulling customer info via a Query of the Customers table. Again, with the relationships set up properly, your subform will work properly.

I still highly suggest you browse the Inventory and Customer service templates. A lof of these things are accomplished in those templates to see exactly what I mean. Honestly at this point I don't know if I'm talking in circles, or out my arse, because I'm a little boozed up to help ease the headache I'm encountering with keystroke trapping at the moment!

 

[TrekMietta]

Okay i'm starting to see what your getting at, just one last question:

i know (slightly) how to create a query, how would i set up the query that you suggested? I'm going to try this you way having seperate forms/subforms etc. Also maybe i'm having a dumb moment but all the table are related using relationships, using either customer number or order number, but what are PK/FK links, i have probably heard of them before just not refered to as PK or FK, what are they, as i'm guessing you've shortend them down.

And once again thank you very much for your help its highly appreciated.

Kind Regards
Trek

 

[PNGBill]

Most Databases would have:

CustomerTable with all of the customer unique details which may well include a postal address and a shipping address.

ProductsTable is details on your SKU items. Description, Supplier, Weight, Packaging, SellUnit, UnitPrice etc

OrdersTable is most likely OrderHeaderTable with the Unique details of each order. CustID, OrderDate, DeliveryAddress(if diff from Cust table) but not postal address or other info that is not different from the CustomerTable

OrdersDetailTable holds all the records of All your Orders from which every customer since day One.
Each record will have, OrderDetailID (uniqueNum), OrderHeaderID, ProductID (sku), OrderQty, OrderPrice

Your queries, forms and reports will make all this "Appear" to be together when in fact it is only linked.

Probably won't be any many to many relationships as even ProductID will be One Product to Many Orderdetail records.
One Customer to Many OrderHeader's.

One OrderHeader to Many OrderDetails.

 

[TrekMietta]

Okay thanks, i still dont understand the Query that i'm supposed to create?? Could you explain a little more please.

Regards
TrekMietta