How do I create my own Order form?? 1

[Minadude]

Hello all! It's my first posting and I've been looking to get an answer to this problem for quite sime time.

I have a database with MS Access 2003 that contains an inventory items table, a customer table, with all the forms and subforms....my only problem is that when I select on the order form the productID from my combo box, the field next to it (product description) does not show automatically according to the chosen productID...

I also need the price to be linked with the previous 2 fields...any ideas?

Thank you very very much in advance!!

 

[ZmrAbdulla]

If your combobox have Product description column too then you can set

[tt]Me.txtBoxDescription = Me.ComboBoxName.Column(1)[/tt]

in the AfterUpdate Event of the combobox.
You need to change the name of textbox/combobox in the code to actual name.

Note: Column Index starts at 0 so Column(1) is second column.

________________________________________________________
Zameer Abdulla
Help to find Missing people
All cats love fish but fear to wet their paws..

 

[Minadude]

It's giving me an error massage saying that the database cannot find the macro.....I'm so confused! let me try to explain what I want again:

I have the following:

Table: Inventory
Fields: ID(PK),ProductID,ProductDescription,SalePrice

Table: Order
Fields: ID(PK),ProductID,ProductDescription,SalePrice

Form: OrderForm
all fields from Order Table.

When I (the user) chose from the cbBox the productID, I want the other fields to automatically display all other info on that productID in the other fields.

Is it a little clearer?

PS: I'm good with very basic Access funtions..please be gentile...lol

 

[ZmrAbdulla]

Two types...
1) You need just display the rest of the fields when you choose an item from a combobox.
Create a combobox through the wizard and choose the third option. "Find a record......"

2) You need to update a record in a different table with items from the combobox.
What I explained earlier..

================================================
Now the error.

You need to enter the code into the code window. You have entered into the property box. There will be an ellipse button [...] at the right side of each event, click on that.. you will be taken to the code window.

hope this helps

________________________________________________________
Zameer Abdulla
Help to find Missing people
All cats love fish but fear to wet their paws..

 

[Minadude]

I don't even see the 3rd option....when I drag a new combo box from the pannel I get the wizard and there are only 2 options in the first step....

Is there a way you can send me a sample database I can just implement in my own? I'm really desperate...I've been resarching this matter for weeks now over the internet and Access books....nothing that I can pin-point tells me EXACTLY how to do it...I will be forever grateful.

Thank you.

 

[ZmrAbdulla]

You won't get the third option if your form is not bound to a table.

Look for Combobox - Populate a Form at

http://www.candace-tripp.com/pages/access_downloads.aspx

________________________________________________________
Zameer Abdulla
Help to find Missing people
All cats love fish but fear to wet their paws..

 

[Minadude]

Thanks I'll keep looking...but can youtell me what exactly am I looking for? what is the term that explains what I'm looking for to do with my combobox?

Thanks.

 

[Minadude]

I think I found it on my own!

It's called populating a form from a combo box!!!

I just need to adapt that to my db....thank you for all your help!!

Minadude

 

[Minadude]

One more problem!!!

How do I update the Combo Box I used to look for the product...basically...how do I interact with all the fields and update them as they change according to the selection made in ANY of them?

Thanks!

Mina

 

[ZmrAbdulla]

You must have the fields bound to the table you need to.. then use something like..
[tt]
Me.txtBoxDescription = Me.ComboBoxName.Column(1)[/tt]

________________________________________________________
Zameer Abdulla
Help to find Missing people
All cats love fish but fear to wet their paws..

 

[Minadude]

I don't understand this statement....what is it that I have to change in it to make it work with my DB?

txtBoxDescription represents which txtBox...the one displaying what I chose FROM the comboBox?? I'm confused Zmr! sorry...take a look at this db from MS access templates:

http://office.microsoft.com/en-us/templates/TC010185481033.aspx?CategoryID=CT011366681033

this db is almost EXACTLY what I'm trying to do...except that I need an extra column in this db which is the productID column as well as the product category....I just can't insert these and make them work out with the rest of the db and relationships!!

please help if you can!

thank you again

 

[ZmrAbdulla]

This sample works with Macro.
[tt]DLookUp("UnitPrice","Products","ProductID = " & [ProductID])[/tt]
"DLookUp" example included in the sample db I have directed.. not with Macro but code(VBA) does the same..

________________________________________________________
Zameer Abdulla
Help to find Missing people
All cats love fish but fear to wet their paws..

 

[Minadude]

I'm still a novice Access user, can you give more details about this DLookUp? Whre do I enter this statement?

Do I have to modify any of my Forms? Tables? I believe I need to add an extra column in the Products table...

Thanks

Mina

 

[ZmrAbdulla]

Open the first sample that I directed. You will find how to use DLookup from that.. It has a clear example..

________________________________________________________
Zameer Abdulla
Help to find Missing people
All cats love fish but fear to wet their paws..

 

[Minadude]

I'm sorry but I don't know what yo mean by "Open" the sample....I know you mean "DLookUp("UnitPrice","Products","ProductID = " & [ProductID])"

but how do I "Open" it?

thx

Mina

 

[ZmrAbdulla]

Open the sample db you downloaded from the link that I have provided(

http://www.candace-tripp.com/pages/access_downloads.aspx

)
Open the form frmComboPopulateMethod4 in the design view , got to the code window (ctrl+g) and see what code is there.
click on the word DLookUp and press F1 key.

________________________________________________________
Zameer Abdulla
Help to find Missing people
All cats love fish but fear to wet their paws..

 

[Minadude]

Ah! Thank you Zameer!

I think I did NOT recieve the link the first time!

I'm trying it right now...will let you know in a bit..

thx

Mina

 

[Minadude]

BEAUTIFUL!!!

Ok so far so good....now here is the tricky part...I need to know if there is a way to have another DLookUp combo box?

example:

in the Sample DB frmComboPopulateMethod4, if I want to modify the ID txtBox into a comboBox. Then I would like to keep the current DLookUP code as it is for the Select Last Name comboBox but also add an ALTERNATE method of searching the table by "ID" as well as Last name. can I just modify the Last Name Combo box AFTER UPDATE and put it into the Afterupdate of the "ID" Combobox?

will my INITIAL DlookUP still work? as well as my new DLookUp?

Thanks

 

[ZmrAbdulla]

Keep in mind..
The sample that I have provided using unbound forms. so DLookUp will not change original data. But in a bound form it will change the data.

________________________________________________________
Zameer Abdulla
Help to find Missing people
All cats love fish but fear to wet their paws..

 

[Minadude]

What is the differance between Bound and Unbound?

Also, if I may, what do youmean by original data? Do you mean the data already stored in the Table?

I only need this for a lookup I already have a form that modifies and adds records into that specific table.

To actually point out what I really need here is EXACTLY what I'm trying to do:

Table: Products
Columns: ID(PK), Stock Number, Description, Price etc..

Form: newOrder
referring to the table Products.

In that form, I have a sub-form with the following fields that are also related to Products table:

Stock Number(Combo Box)
Description(Combo Box)
Price(txt)
etc..(more fields non important)

When the user opens the "newOrder" form, they can search from the "stock number" combo box the appropriate column in that table and when they find the stock number, all fields regarding THAT STOCK number will be populated with the appropriate info....BUT if they decide to search from the "Product description" combo box instead, all other fields INCLUDING THE STOCK NUMBER field will also be populated with the info regarding the Description they cose! that is exactly what I need my form to do in PLAIN ENGLISH words! lol

Thanks Zameer!