Combo box help

[cubbyamber]

I have been searching this site and I have found numerous postings that people are having the same problem as I am but I still can't seem to grasp it so I am posting myself.

I have a form that has 12 fields plus a subform. The fields are Client Number, Client Name, Client Address etc. The Client number is a field that I give a value to and it is also the primary key.

what I am trying to do is to put in the client number (which I have given)and it will bring up the name, address, etc for that specific client. How do I do that?

I found these two threads that I think is what I am looking for

http://www.access-programmers.co.uk/forums/showthread.php?t=113392&highlight=combo+box

http://www.access-programmers.co.uk/forums/showthread.php?t=112958&highlight=combo+box

I still don't quite grasp it though.

For instance: How do I know what my combobox name is? Once I figure that out, where do I put this code at?

Thanks in advance.

Tracy

 

[Len Boorman]

If you look at the properties of the combo box you will see the is as the first entry.

The name of the combo goes into the criteria of the query that is the record source of the form that is going to display the client details

The syntax of the criteria is
Forms![Form name containing the combo box].[combo box name]
Len

 

[cubbyamber]

I have tried that and it still doesn't seem to be working. This is what I do

Open Form In Design View
Change Client Number to Combo Box
Row Source Type is Table/Query
Click on RowSource click the three dots
Opens SQL Statement:Query Builder
Select Table Clients
Then In Field:
client Number first Field, Client Name Second Field, Client Address Third Field etc
Then under each field where it says criteria I type in the code that was given to me.

I have done it with and without adding .column(0) etc.

Tracy

 

[Len Boorman]

We are not understanding each other i think
My undewrstanding

1) Your Form has a combo box and you want to select a client number and use this to open a form to display Client Details
2) You need to create a new form for the client details, which will have a query as its source.

so Going back to Form 1 and the combo box.
Yes it will have query to supply data for the combo box. One element will be the client number, preferably the first column. Make sure bound column is set to 1 in the combo box properties.

Now the second form to how the client details

The supporting query must have the client number as one of the fields. The criteria for this column only is the Forms![Form Name].[Combo box name]

If you put this in any other field it will not produce a result because you will only find the client number in the client number field

Finally you set the on click property of the combo box to open the form. So sequence is

Open form 1
select client from combo box
On click property event procedure fires
Opens client detail form using its own query
Query looks at value in combo box to determine which client number to use
query extracts data and form displays data

Len

 

[cubbyamber]

I think we are mis understanding each other as well.

I have a form here are the fields in the form:

Client number
client Name
client Address
client City
client State Etc

I want to be able to enter or start entering a client number on the form and it automatically fills in the client address, city, state etc on this same form.

so if I have a client number such as TC and the client name name is Tracy Collins the address is 123 AnyStreet the city is Anytown the state is Anystate.

I want to be able to enter TC in the client number field and it automatically fills the form with Tracy Collins, 123 AnyStreet, Anytown, AnyState.

I hope this makes sense.

Tracy

 

[Len Boorman]

Okay gotcha

Basically it is a question of using an Event Procedure against the on click property of the combo box.

Lets say combo box is cbo_1
On the form we have form text boxes
txt_Name
txt_Address
etc

Source of cbo_1 is select ID,Name,Address, etc from tbl_Client

On Click event procedure is
txt_Name=cbo_1.Column(1)
txt_Address=cbo_1.Column(2)

I am a bit rusty on the exact syntax but think this is about right

The combo box references start from 0

Hope that helps a bit

Len

 

[cubbyamber]

Please forgive my ignorrance as I am very new to the advanced access. I still am not following so I have attached a copy of my database. I'm hoping you can take a look at it and give me some additional insight on it especially with having it right in front of you. The names are all fictitious of course but it's enough to get the job done.


Thanks in advance

Tracy

 

[cubbyamber]

I got the autofill figured out but I ran into a couple of issues.

My client number is my primary key so there is not allowed to be duplicates so here are this issues:

1. I can't enter any new information into the field. Such as a new client. I can put in a client number but it won't let me fill out anymore information (in the form).

2. My subform: it doesn't let me scroll or enter any new information into the subform if I go to the second client. It comes up with an error that says "The changes you made were not successful because they would create duplicate values in the index, primary key or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again" It works fine on the first record but not any additional records. Nothing is duplicated.

However, nothing is duplicated.

Tracy

 

[Len Boorman]

Had a look
I do not see the need for the two order tables. An order is an order and all attributes of an order should be in a single table

I suggest that you separate Enquiries, new data and Edit of existing data

yes it probably can be done on one form but in practice I suggst that it will allow errors to creep in

I have created an enquiries form with a combo. Select the client and your clients form opens with details of the client and any order/s
Yiur data needs normalising. In Order you have client name again but this time split names. The Client PK is enough. That gives you the link to the Client data

Okay so I realise that you want to press on but the table structure and relationship links are the most important. Get these wrong and you will have problems all the time

The duplicate message you are getting is because it is trying to add the data again, not edit it

len

 

[cubbyamber]

Okay, I agree that their is no need for a second order table. I will delete that one. I got this database from a client and he had already started working on it, he just had the tables done and realized it was too much for him to do so he handed it off to me.

How do I separate inquiries and if I do, will it automatically update the existing data so that we can type in another client number and it brings up the existing data.

As far as the client name in order split up. I will tell you what this is used for and hopefully that will shed some light on what I am doing.

My client is a Private investigator he gets orders from people (which is where the client name and number comes in) and he needs to keep track of the people he is to locate and that is where the First Name Last name comes in in the order details subform. So actually the client name and first name and last name in the subform are different people. hope this makes sense.

Also in your inquiries form, when I click on the second client number (CE) it brings up the data for the first client, client number(AB) Any ideas on this one?

Tracy

 

[Len Boorman]

Okay

I try to keep things simple and so I have an Opening Form from where I can go to
Add, Edit, Enquire on the data

Enquire I have shown you
Add is split into Add new client and add new order. Separate, simple
Edit
Again edit client details and edit order details

Read the PM I have sent and let me know your thoughts again by PM

Always forms re based on query or SQL statements, Never directly to a table, or at least not often. I tend to use sql statements now although earlier I used queries

A form can be opened as Read Only, Edit or Add (where you get a set of blank fields.

Much easier to control

Len

 

[cubbyamber]

Len,

I just wanted to let you know that I sent you a PM back along with my email address.

Tracy

 

[cubbyamber]

I understand what you are saying about the records. Here is my question for you:

If I can get the lookup of the client set up right, can I just add buttons that say Add Record( which adds a new client) and Edit record (which allows like a change of address on the form? And then Can I also just put a button on the Form that says add new order or edit existing order? This is what I actually wanted initially.

Let's say I am in the form that is read only. At the bottom of the form I want a button that says Add Record and when I click it, it brings up a blank form. I also want a button that says edit Client information. You click on it and it brings up the form in edit mode. I hope this makes sense.


If I can just add the buttons that is great, I am not ready to do that yet. I will start adding buttons when I get my queries and forms figured out.

So here are a couple of more questions for you:

Question 1: Can I just add buttons to my forms to add records and edit records?

Question 2: How do I get my inquiries form to work correctly where it brings up the right information for each client?

Question 3: You said to base my forms on queries or sql statements. Since sql statements are much more advanced than I am I would like to base it on a query. To do this, I need to just create a query using all the fields from my Clients table as well as the fields in my order details table right? I don't have to worry about sorting anything right?

Thanks

Tracy

 

[Len Boorman]

Question 1: Can I just add buttons to my forms to add records and edit records?

Yes
The On click property of the button can be used to open another form. I suggest separate forms for Add and Delete

Question 2: How do I get my inquiries form to work correctly where it brings up the right information for each client?

Use a combo box to select the Client and use this as the criteria eithin a query, The query is the source of the edit form.

Question 3: You said to base my forms on queries or sql statements. Since sql statements are much more advanced than I am I would like to base it on a query. To do this, I need to just create a query using all the fields from my Clients table as well as the fields in my order details table right? I don't have to worry about sorting anything right?

The sort can go into the query. A query is an SQL statement. Look at a query in design view and then seleect View...SQL from the main toolbar and you will see the sql statement. This can be pasted directly as the source of a form and acts the same way.


Len