Entering a Particular number to show all info (1 Viewer)

[Tech]

Hi there.

Right, I am now on the hard part and need some help, ASAP if possible please

I have created an order form, this order form will have the customer details (which a table has been made) and the Product Details (Again, table has been made for this)

I created forms for both of these tables, infact 3 including the order form itself.

Right, What I want is, IF I created a customer details subform in the order form, IF I enter a Customer Number (Which Contains the Customer Number as a primary key), and IF that Customer EXISTS in the database, display all the information about them (like name, address etc...)

IS it possible to do this in MS Access? If so, please explain step by step how it's done, I GREATLY appreciate any helpful advice, I REALLY need this to work.

Thank-you, take care

 

[bjackson]

you should be able to do what you want by using a record
source for your subform based on a query that returns
the customer information based on your order customer number

 

[Tech]

?? I don't quite understand....

There is no query involved yet......unless this is the only way to work then put this query as a subform...but then, if we do do it this way, there is no way that the query would allow us to enter new data, if the customer does not already exist...

please explain step by step on how to achieve the solution to the problem I am having.

it's so hard... I haven't done this type of stuff before...

Thank-you

 

[bjackson]

its a bit hard to follow your project

if i was doing what you want to do i would do this

1.on my orders form i would have a combo box whose
record source was my customers table.give this combo box a
name of say CustName

2.as soon as you pick a customer from the list then
all the customer details would appear in text boxes on my
order form, i would not use a subform for this purpose,but it
does depend on your particular project


3.i would have a separate form for entering customer details,i.e the customer must exist in the database before placing a order
as i have found customer databases have a lot of varying information to be stored about them

4.on the combo box not in list event , i would have code that
opens my form for customers and i would add a new customer that way

5.the combo box for customers would have a query as a recordsource.the text boxes on the order form would then
have their control sources set as
eg
if you had a textbox called CustAdress its control source
would be =[Custname].column(1)

if you had a textbox called CustPhone its control source
would be =[Custname].column(2)
the above is only an example as i dont know what you customer
table is like

i am sorry i cant be more specific but its hard with limited knowledge of your database set up

 

[bjackson]

have a look at this sample.It was done in a hurry so
its not real neat but check out what i mean with the text and
combo boxes

 

[Tech]

u all have been very helpful, thank-you for the reply perfect

I like that sample, that is the one I like to have......

I am wondering, instead of using a combo box, can we just use a text box, and then on the (maybe) the enter event, it should display all the customer details?

Thanks again

 

[Tech]

lol...here i come again!

It won't work for any other field

I mean, if we select a Customer number, only the Customer name will appear on the text box, but on the rest of them, the appropriate information does not show, just blank

it only works for the name (=CustNum.column(1))

=CustNum.column(2) is the Address field.

Any ideas? Nothing shows up in that text box

 

[Tech]

The Other problem I am facing is that, if we enter a new record, whatever the customer number was chosen last, will be in all the records is there a way to stop this happening?
I give up!

The problems are this:

1) For the rest of the text boxes, it does not show the details of the customer, according to the customer number, it only shows the name

2) If we ignore 1, and we continue, and we add a new record and place an order, the previous records, show the same information as the new record, this is not good! So in record 1, if we enter Customer number ABC123 and fill in the rest of the details (like price etc..), if we add a new record and type in Customer number DEF456, and we fill in the details again, if u click back, the Customer number DEF456 will appear through out all the records! but the other information will differ (like price) which is great, it's just that Customer number that will not be "held" in the record. How do I sort this out?

3) A similar thing, like the customer table, will be placed with the Product Details.

Help!

 

[bjackson]

can you zip your database and send it to me

 

[Tech]

i need ur email addy

 

[bjackson]

attach the db to your reply

 

[Tech]

it's too big for it, even after zipping! after zip, it's 154KB..i will upload on my server:

ftp://temp:temp@techftpserver.co.uk/mdffc.zip

 

[Tech]

I hope that link works....

 

[bjackson]

OK it looks like your not familiar with combo boxes
i have to go to work shortly but i had a quick look

you have to set the column count property of your combo boxes
to match the column count of their underlying query
then your text boxes will display the right value

you also have to set the column width values
in your case for customer name combo box it would be

column count=8
column widths=0;2;0;0;0;0;0;0

i have run out of time but i will have a look at the rest after
work (i do kitchen cutting lists for several factorys)

 

[Tech]

thank-you, I DO Appreciate ALL OF THIS SIIRRR!!!!

thank-you....

I hope i can get this to work..

 

[Tech]

ur a genius It works, many many thanks

I have the other problem - lol, sorry.

it's when, if you create a record, then add a new one, whatever the last records customer details/product orders were, it is still kept and moved on to the next record! and if u change it, and go back to the previous records, it's been replaced!

and also, hopefully once this problem is sorted, obviously, if we enter a new record, and still put in the same customer number, it should make a new record right? and if we browse through the order numbers, it should show all the orders created/ordered by the custoomer right?

I hope.......

how come?

 

[Tech]

I mean, I could delete it and simply make a subform or something, or tell it to use the appropriate tables/fields as the control source but we wouldn't be able to get the info put in there from the combo box, if we select a customer/product number

why so hard?

 

[Tech]

sorry for all these msgs, i do apologise.

i was looking at the northwind db, is there a way to get the datasheet view and the drop down lists like the one in the orders section? that would be great...

 

[bjackson]

have a look at this sample
i will help you as much as i can but you must
do most of the work yuorself,other wise you will not learn

this db is how i would tackle your project,it is not
necessarily the best way as i am only a novice myself

 

[bjackson]

i forgot to mention you will have to change the
currency format to suit and also set the text box next to
custnum to be not visible