Previous data

[bdb25]

Hey there.

I've got an orderform in access, which has employee details on, delivery date, completion date and alot of customer details on, there is also a subform with product details and quantities in but that doesnt matter.

I am wondering how i can in someway create a button, or just a search box, in which i can type a previous customers name, and their data be inputted into the form, because otherwise its going to waste time, and annoy the customer if every time they come to make an order their details have to be entered.

i have

ID
CusFirstName
CusLastName
CusTitle
CusAddressLine1
CusAddressLine2
CusAddressLine3
CusPostcode
CusPhonenumber
CusEmail

i somehow want to search the tblCustomer and input the data, btw if it makes it easier there will only be one person with a certain surname. (possibly a listbox?) i just dont know how to link it all together.

If i havent been clear enough tell me and i'll try and explain further.

Thanks alot

bdb25

 

[ansentry]

If each time you create an Order you have to input the customer details manually then you table structure is incorrect.

Have a look at my sample I posted here it is called "Combo to fill in controls". This should give you some ideas, you will notice that 1 Customer can have many records BUT the customer information is only stored as a Key (number) in the data table and not all the details e.g name address phone numbers etc.

This example is very basic, I have not included a customer form, where you would input customer details (for new customers) but you should get the general idea.

If this is what you want to do, then when you understand what is going on, post back and I will point you to an example that will do more (it includes a subform for items sold, pricing etc.)


Good Luck.

 

[bdb25]

Thats just what i was looking for, and i've been able to set that up, however when i'm creating a "new order" i also have to put in employee details and product details etc, when using the method you described, the previous order information is also inserted into the table.

I've attached it, its in a very very simple form btu i want to get it working before i play about with the looks of it.

Thanks again

 

[ansentry]

i've been able to set that up

No you haven't, have another look at how my example works. Also have a look at the orders and orders details forms and their sources from the Northwind database.

 

[GaryPanic]

chipping in on this

you need a customer table
which will hold all your customers details
then you need an order table

with a choice of what to do next you will end up with an order table
and this will have a customer number in it- or customer details in it -depending on how you do this (for the purests it should only be a number with a look up ) and then this will form an order - you then can attach products to this

iof you look at the view form in my sdample d/b and the policy list - this allows mulitple items tied to the main record
and if you look at the customer form in the other one you will see what we are trying to get over to you
if you select a product it will fill in the spaces on the form

so if you set up a customer table - tied a qry to this table use this qry as your source for the combo box and use of the after update on this combo box to populate fields in your table
this will give you a tabled structure
allowing
1 customer to have more than one order and on that order to have more than 1 item
so

J Smith can have an order in May for 4 items
each of these 3 needs to be quantifed J Smith (needs to be identified ) customer table unique number - order again a unique number so a sepearte table is the best option and also an items table again each item will have a unique number - now in my "rough" sample the items can be anything so free form text is allowed - however you can tie this down to spefici items in an another table

these 2 samples shown are for other examples so it is a case of hack them see what they are doing - and mirror it in your own d/base

g

 

[GaryPanic]

OK - just added a very rough customer entry form
I have put in customer name and 1st line of a address
now look at the form

 

[GaryPanic]

pushed the button before I should of
you will see- on the form is a find customer selecting this will populate field on the "main" form

now as the customer has a unique record we can see how much we have done for them by the use of qry's

now if you marry this idea with the mainform on the "View" d/base (I know bad name for a d/base) the two ideas will allow 1 customer with orders on these orders you can have mulitply products/items

 

[bdb25]

hey ansentry thanks for your help so far. i'm using access 2007 and it doesnt have the northwinds example database.

As for your example i can see that the customer details are a combo box that looks up data, but i cant see how it ONLY looks up customer data.

Sorry i'm not very experianced with access.

Thanks

 

[boblarson]

The northwind database is available in 2007 by selecting Samples from the main screen that opens when you open Access by itself without opening a database. It will download the first time you go to it.

 

[bdb25]

thanks very much for your help so far and i've now downloaded and had a little look at the northwinds sample data base, first thing i can say is very complex. but i think i get some parts of it i've been trying again and again to get this part of my database to work and its seriously stressing me out.

I did at one point manage to get it working so that when a customers surname was chosen the rest of the data filled in, however i managed to loose this :S i dont know how, when i tried to do the same for the employee details.

Then i got to the part where i tried to get the subform to enter the rest of the data automatically when a product was picked (pick a product- price and ID appear) i cant get this to work either.

More help would be greatly appreciated.

Thanks again

(attached is my latest version of the file..... things may be a bit messy)

 

[ansentry]

Here is your first example back, I have corrected a few "errors".

You will notice that the combo for Titles has been removed from the order form. You will also see that I have added a form for you to add customers to

Have a close look at the queries that are the sources for the forms and combo boxes.

To repeat myself:

Post back and I will point you to an example that will do more (it includes a subform for items sold, pricing etc.)

 

[bdb25]

Hey thanks for not giving up on me just yet.

And sorry for not reading properly.

Was there meant to be an attachment to that last post of yours ansentry?

Here is your first example back, I have corrected a few "errors".

 

[ansentry]

Sorry, here is is.

 

[bdb25]

i've just managed to do it, and now i've done it its really quite simple isnt it?

i was messign around trying to change things that are tottally unrelated.

Thanks for all your help in getting me there.

 

[ansentry]

Glad to help.