Listboxes

[djeeten]

Hi,

this probably is a stupid question, but I cannot work it out myself.

I have this table tblAnalyticalCodes with 3 fields in it:

-Analytical code
-Description
-Groupcode (this is the code of a main group)

there are 3 tables involved here like this:

tblProducts n--1 tblAnalyticalCodes n--1 tblMainGroup
ProductId AnalyticalCode Groupcode
.... Description .....
AnalyticalCode Groupcode

Now, I have this form frmAddProducts, where you can fill in everything about a product, including the analytical code. The user is able to choose an analytical code from a listbox, based on the table tblAnalyticalCodes.

SELECT [tblAnalyticalCodes].[AnalyticalCode], [tblAnalyticalCodes].[Description], [tblAnalyticalCodes].[Groupcode] FROM tblAnalyticalCodes ORDER BY [tblAnalyticalCodes].[AnalyticalCode];


Now I would like there to be 2 listboxes, both based on the table tblAnalyticalCodes, but one showing the codes, and one showing the descriptions. I can manage this, but I haven't figured out how to make sure that, when I change a value from one listbox, the matching value in the other listbox gets shown.

Thanks a lot in advance,

dj.

 

[fneily]

I'm not sure why you just don't want the analytical code and description appearing in the same list. It seems you're creating to much work. But, you can just create a duplicate listbox as did for the analytical code. Then write code on the, let's say, DoubleClick event for the first listbox that says something like:
Dim SQLText
SQLText = "SELECT description FROM tblAnalyticalCodes " _
& "WHERE AnalyticalCode = Me![FirstListboxName]);"
Me![SecondListboxName].RowSource = SQLText
Me![SecondLIstboxName].Requery

Neil

 

[randysmid]

Hi,
I use an interesting technique that allows the user to maintain a personnel database, and this form lets the user select via SSN combobox or LastName combobox. I use the AfterUpdate procedure to do this processing. Here is the code:

Sub cmbSSN_AfterUpdate()
' Find the record that matches the SSN
Me.RecordsetClone.FindFirst "[SSN] = '" & Me![cmbSSN] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
cmbName.Value = Last_name
End Sub
'***********************************

Sub cmbName_AfterUpdate()
' Find the 1st record that matches the full name.
Me.RecordsetClone.FindFirst "[Full_name] = '" & Me![cmbName] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
cmbSSN.Value = SSN
End Sub
'***********************************


HTH,
Randy Smith
California Teachers Association

 

[fneily]

I'm confused. You say they choose from a combo box showing "LastName". In your code, you mention "Full_Name". What do you do with six "John Smith"'s? In my college where I teach, we have duplicate names all the time. So SSN would be appropriate. But then again, why not combine SSN, Name into one list box (or combo box)?

Just curious.

Neil

 

[randysmid]

Hi,
Yes, if you have hundreds or thousands of names to choose from, then combining Full_name and SSN would be an excellent decision. In my case, I only have a hundred or so names. By the way, Full_name is a custom field I built in the query. I wrote an FAQ on it: faq701-3273

HTH,
Randy Smith
California Teachers Association

 

[fneily]

RandysMid
Just for fun. You're talking hundreds, etc. samples. If you are ever in a group of 32 people, bet $10 dollars that there are two people with the same birthday. The odds are with 32 people 95%. The chances that two people have the same name with "hundreds or thousands" is a bet you don't won't to do.

Neil M.S. Mathematics M.I.T.
M.S. Logic Johns Hopkins University

P.S. I'll assume you can figure the birthday odds on a group of 32.

 

[djeeten]

Hi fneily,

I don't want the analytical code and description to appear in the same list, as the user should be able to search the analytical code by its code or its description. That's why there should be 2 listboxes, you see?

I'm still kind of stuck here though, I tried to put this in the afterupdate events:

Private Sub Description_AfterUpdate()
Me![AnalyticalCode] = Me![Description].Column(1)
End Sub

Private Sub AnalyticalCode_AfterUpdate()
Me![Description] = Me![AnalyticalCode].Column(1)
End Sub

--------------------------------

Now, the AnalyticalCode_AfterUpdate event does it initially(when I choose a Analytical code the first time, the value in the other listbox will change as well). But when I try it again, I get an error saying that I need to save the record first, if I want to change the field.

Any help is appreciated,

Thanks,

dj.