One more combo box question

[jeremy0028]

Thanks for all your support database is almost finished
last one work fine

I have a table & form called patient info and a table & form called charges

In the charges form i have a combo let say combo59
that looks up patient info table first name, last name, MI

and stores that information into the charges table under a field called patient

the problem is it only stores the last name
I tried using this in the combo59

SELECT [Patient Info].[Last Name]& ", " & [Patient Info].[First Name]& " " & [Patient Info].MI AS FullName FROM [Patient Info];

When i use this code it works fine store the information in the patient field in the charges table like this
last name, first name MI into one field



However when i generate a report the information doesnt come out

Example

When i use the report wizard i select patient from the charges table and from the patient info table i select address city state and zip

When i dont use the & " " & in the combo box the information comes out however only displays last name

?
?

Should i create first name, last name, mi in the charges table

If so when using the combo and select from patient info table last name, first name, mi to store those name in those fields how do i put all three in there in there places without putting in a single field.

 

[Lilliabeth]

What is the primary key in the Patient Info table?

 

[jeremy0028]

The primary Key is on account # which is set as auto

 

[Lilliabeth]

This is the field that should be present in the Charges table. That is what primary keys are for - to enable relationships. Add that field to the Charges table and relate the two tables on this field. Since account# is an autonumber in the parent table, it must be a long integer in the Charges table.

Then walk thru the combo bix wizard and choose LastName, MI, Firstname, and Account#. Tell the wizard to store account# in account#. Amazingly, it will display the name info in the drop-down, but store the key field - as is right and good.

If you want the combo box to look prettier, you can create a query that concatenates the name info, but you still need the account#, and then use the query as the basis for the combo box.

The report will need to include the patient info table as this will be the only place that has patient names stored. A very major goal of a database is store each piece of data in just one place.

I encourage you to spend a little time reading about relational databases.

 

[snub]

I have an inventory control database that allows me to add and sell products. On my frmAddNewProducts form it asks for Manufacturer and ProductName these are in combo boxes. The combo boxes are linked so that the cboProductName box is dependant on the cboManufacturer box, this works well. I would like to set it up so that I can add new products that are not currently in the list, I have a tblManufacturers and a tblProductName that are feeding the combo boxes. I have code in place now that will allow me to add the new products but I can't get the two features (adding new products and the second combo box being dependant on the first) to work together. Any suggestions. I searched the FAQ's and couldn't find this scenario.

 

[TheAceMan1]

How are ya snub . . .

Your problem is not related to this thread. Starting a new thread for yourself will give you [blue]full exposure to the forum[/blue] (a great many guru's here), and allow others to benefit from any resolution. ;-)

See Ya! . . . . . .