Need Form Help Please Combo box issues 1

[sparky01]

I need help,

I have a databased that needs to be very automated. The user wants to be able to click on a combo box in a form and pull in records from antoher table that will fill in the form fields so that they can be stord in another table.

First is this possible??? and what can I do to make this happed.

PS. would it be better to use a macro?????

David

 

[scriverb]

Yes this can happen with VBA code from behind the combobox. But, if you can have a table that can be linked to your table through a common field we can have this done automatically for you. We can create a query that will make that data available through the forms RecordSource.

Post the details of the tables, fields, relationships, etc. that you have and I can give you some code to get you started.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[sparky01]

Ok, the table withthe combo box has a on to many relationship with a client table that lists the data that I need.

The Job location table is tied to a form that populates said table. The Combo box on said table is linked to the client table so that it can lookup the values needed. i will need to be able to pull in five fields from this table. if you can tell me how I would really be grateful.

If you need a copy just give me your e-mail

David

 

[scriverb]

The row source for your combobox should be a query or SQL that includes all five of the fields that you need to extract from the Clients table. Just list as part of the select along with the first column being the visual identifier for the user to pick. The Bound Column should be set to 1, the columnm count set to 6 with the column widths set to: 1;0;0;0;0;0 Now the columsn 2-6 are available to be assigned to your Job Location table controls on your form by using the following example VBA code in the AfterUpdate event procedure of the combobox:

Me![JobLoc_Field1] = Me![Combo1].column(1)
Me![JobLoc_Field2] = Me![Combo1].column(2)
Me![JobLoc_Field3] = Me![Combo1].column(3)
Me![JobLoc_Field4] = Me![Combo1].column(4)
Me![JobLoc_Field5] = Me![Combo1].column(5)

You see that the columns 2-6 are referenced 1-5 as the numbering scheme of the column property starts at 0.

Let me know if you need more assistance here.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[sparky01]

Ok, the table withthe combo box has a on to many relationship with a client table that lists the data that I need.

The Job location table is tied to a form that populates said table. The Combo box on said table is linked to the client table so that it can lookup the values needed. i will need to be able to pull in five fields from this table. if you can tell me how I would really be grateful.

If you need a copy just give me your e-mail

David

 

[sparky01]

Sory to sound so dumb but is this all in need to do or is there more????

David

 

[scriverb]

The Row Source for the Combobox should be a query that takes in the Client table. When you pick the client from the combobox all of the 5 fields will be available but not visible if you follow the directions above. They are referenced by using the Me![Combo1].column(?) syntax.

That code should be in the AfterUpdate event procedure of the ComboBox control. That is all you really need to do.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[sparky01]

Thanks for all the help I just don't want to seem dumb again can I build this query through the wizard or is there a better way???

David

 

[sparky01]

It looks like it is trying to work but I need to understand the column numbering better

David

 

[scriverb]

Send me your client table info. Name of table and the names of the fields that you want to be displayed and returned. I will send back the SQL for the row source.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

David: I have your file and need to know if you want these records condensed. You multiple records by Name with some of the rest of the info being the same and some different. How will you have your users pick which record in the combo.


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[sparky01]

I will have the user pick from a combo box in the Joblocation field

David

 

[scriverb]

Here is the SQL to use in the RowSource for the ComboBox. Update it with the correct spelling of each of the fields and the table name if necessary.

SELECT Client.[Client Location], Client.[Client Name], Client.Amount, Client.[Material Hauled], Client.[Time To Pickup], Client.[Time To Deliver]
FROM Client
GROUP BY Client.[Client Location], Client.[Client Name], Client.Amount, Client.[Material Hauled], Client.[Time To Pickup], Client.[Time To Deliver]
ORDER BY Client.[Client Location];

Now the code to update your form is as follows:

Me!strclient = Me![Combo10].Column(1)
Me!strclientlocation = Me![Combo10].Column(0)
[red]Me!Material = Me![Combo10].Column(3)[/red]
Me!timeup = Me![Combo10].Column(4)
Me!timedeliver = Me![Combo10].Column(5)
Me!numclientpay = Me![Combo10].Column(2)

The columns above match up to the query that I provided. All but the red looks okay. Your code you sent me had Me!strState. There is no State data. There is Material Hauled column. You will have to figure that one out. Just change the control name to Material or whatever you have called it but the column is correct.

Let me know if this helps.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]