Query from Combo Box must display records in Text Boxes 2

[frummel]

MS Access 2002:

I have a table called ClientData.
The table contains records: ClientName, ClientAddress, ClientAccountNumber.
In a Combo Box I select an entry from ClientName records.
What I want is: When selecting a ClientName, the ClientAddress should appear in a Text Box, and the same for the ClientAccountNumber.

How can I achieve this?

 

[frummel]

Yes, thank you! That was exactly what I meant!
I guess you really earned those 2 stars!

 

[Annelies]

Hi everyone!

I'm having a similar problem, but mine is compounded by the fact I'm trying to do this in a datasheet.

I have two fields - a combo box called lstFileNo and a text box called txtInsured. I would like the txtInsured box to populate depending on the FileNo selected in lstFileNo. There is a table which links the two together. So far, I have managed to get it populating with the correct information, but the problem is that it populates all of the txtInsured boxes on the page, not just the one in the appropriate row.

Can any one help! I have been going in circles with this problem!

Thanks muchly

Annelies

 

[Edski]

Annelies,
Is your datasheet a subform within a main form? It may be that the subform isn't linked to the main form. You may need to explain how your database is structured a bit more.
Cheers.

 

[Annelies]

HI Edski

Yes, the datasheet is a subform within a main form. All the working happens on the subform though (there are no links, per se, back to the main form - the main form is just a 'pretty' surround for the datasheet).

I basically have a table called FILES, which (among others) contains the fields FILENO and INSURED. It also contains a table called TIMESHEET, which stores time against a FILENO.

Basically, this form lets users record their time against a file no. So the user selects a FILENO from a combo box and then proceeds to record time against that fileno. What I want to do is have another text box which displays the INSURED party associated with that FileNo (for the users reference - so they can double-check they selected the right FILENO). The FileNo will change for each line in the datasheet, and therefore I need the textbox which holds the insured to be relevant to the FileNo only in that row.

I hope this all makes sense and provides a bit more info

Thanks

Annelies

 

[LeeJames75]

With regards to the first solution.

what can I change should the existing text fields be bound to a table ?

Fields 1 - 5 are bound to a query from table1 which does a calculation. Then I have my list box showing me 3 columns which is from table 2.

Then I have fields 6 - 7 which are to be populated from table2 using the list box and stored in table1.

I have used the afterupdate function for my listbox Anonymous_ID.

Practice and Area are from my table 2 and will be stored in Table 1

Private Sub Anonymous_ID_AfterUpdate()
Me.txtPractice = Me.Anonymous_ID.Column(2)
Me.txtArea = Me.Anonymous_ID.Column(3)
End Sub

However when I click on a list item , I get "Compile Error - Method or Data Member not found".

What do I have to add/change code wise ?

Remember my fields are bound to a query.

Regards

Lee James
Data Analyst

 

[LeeJames75]

Ok.

I have solved one problem. But now I have encountered a bigger on.

As stated, my combo box has 3 columns, that contains data from table1 which the user enters.

Column 1 has data, column 2 has data, column 3 has no data.

When the user clicks on data identified in column 1.

Column2 data appears on a text field. But Column 3 which is blank, falls over with

Run-time error '3315'
Field Raw Data.Area' cannot be a zero-length string.

This is my code in the afterupdate function


Private Sub Anonymous_ID_AfterUpdate()
Me.txtPractice = Me.Anonymous_ID.Column(1)
Me.txtArea = Me.Anonymous_ID.Column(2)
End Sub

How can I accept blank values in Me.txtArea ?

Regards

Lee James
Data Analyst

 

[LeeJames75]

Just to let you know I have done a temporary fix.

In table 1, I have set the DEFAULT VALUE to all my fields as
"No Value Entered".

This now works.

If anybody has another solution. Please let me know.

Thanks to all those people in the original solution.

Regards

Lee James
Data Analyst