Filling a table using combo box

[BIGGY]

I have one table (tblComplete) with a listing of 30-50 company names and associated information...

"tblComplete" has fields: AutoNumber, Company, Address, City, State, Zip, Phone

On a form I created a combo box linked to AutoNumber and Company to drop down the list. Once the 'company' is selected, I would like it and the rest of the fields (address, city, state, zip, and phone) to go into the blank table (tblSelected).

Could I use a Macro to do this or should it be a query?

 

[llkhoutx]

The combobox should have all fields in the table, with control height of all columns, except for Company's, set to zero.

Then on the OnCurrent and cboCompany AfterUpdate events,

me.address=me.cbocompany.column(2)
me.city=me.cbocompany.column(3)
me.state=me.cbocomme.cbocompany.column(4)
me.zip=me.cbocompany.column(5)
me.phone=me.cbocompany.column(6)

A purist would advise, City, State, and Zip are obviously not normailized. Consider adding City, State and Zip tables with autoindicies and saving their respective index in your exisiting table. The foregoing code would still work, but the cbocompany recordsource would be a multi-linked table.

I keep believe in keeping it simple and my mdbs are not necessarily normailzed in all respects. A simple table like you have alawys works, even though not normalized.

Overall mdb design is paramount. If other tables have addresses, then in that event, normalized tables should probably be utilized.

A normalized table has atomic data, that which uniquely identifies a field. Atomic indicies can repeat in a given field. One of the ideas behind this if that a string name take more space than a byte, integer, or even long index. To me, disk space, memory are not paramount concerns like they were with 64K memory PCs and no hardedrives. None thye less, normalization is generally a best practice.

 

[itprog]

llkhoutx,
Your reply to Biggy helped me with a similar problem I was having. I am using the code to fill fields on a form, not a table. I am having one problem. In one of the text boxes on my form I would like to fill in the date from the table. I keep getting an error message that there is null data, yet if I run the query outside of the form there are dates. My other text boxes fill fine using the method you posted. Any idea why dates would not work?

 

[llkhoutx]

You say

I would like to fill in the date from the table

but don't say if your using a bound textbox or a DAO/ADO recordset. If your using the former, i.e. a bound text box, there ciuld be two problems: (1) the ControlSource property for that textbox is incorrect, or (2) the Format property for that textbox is incorrect. I suspect that the Format property is incorrect.

 

[itprog]

I am using a bound textbox. The control source is a field from the query that populates the form. The format property is short date, which corresponds to the property of the field in the table.

 

[llkhoutx]

You appear to be doing things correctly.

Execute the query in the QBE frame to see what if you get a Null in the subject date column.

 

[itprog]

When I run the query I have dates in the field - no null values.

 

[llkhoutx]

You also said

I keep getting an error message that there is null data

When does that data occur?

I recall getting that error when I attemp to stuff a Null data value in a bound or unbound field and/or a recordset field.

Maybe you're referencing a Null field and are trying to store that value.

Try to trap the error with the Debugger.

 

[itprog]

What I have is a medical record review form. When the form opens, you can go through records that are already entered for patients. At the top of the form is a combobox that shows the patient id - when you click the arrow to select a patient, I show the id#, last & first name. When I add a new record, I would like to fill text boxes at the top of the form with the last, first name, location, and admit date of the patient chosen from the combobox. The query for the form draws data from the patient table and a review table. The patient id is the primary key for both tables. The patient table contains valid dates in the admit date field that I use in the query. When I choose a patient id# from the combo box, I use the afterupdate event to run the following code to fill the textboxes at the top of the form:

Me.txtLName = Me.cboCNO.Column(1)
Me.txtFName = Me.cboCNO.Column(2)
Me.txtWard = Me.cboCNO.Column(3)
Me.txtAdmitDate = Me.cboCNO.Column(4)

The last line generates the error that there is a null value and is trapped by the debugger. The previous 3 lines fill in the correct data. The admit date field in the query does contain valid dates.

 

[llkhoutx]

You have to test for nulls prior to storing on a form, as they can't be stored directly.

if not isnull(Me.cboCNO.Column(4)) then Me.txtAdmitDate = Me.cboCNO.Column(4)