listbox values by combobox return value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

Within my db I have a table containing BusinesstypeID and BusinessName.
I have created a form with a combobox wherein I can select the BusinessName
and the BusinessID is the result. (checked it with a msgbox).
Now within the form I have created a listbox in which I want to display all
CompanyNames ( from the table Contacts) having this BusinesstypeID.
The vba code used
Private Sub ComboBusinessType_AfterUpdate()
Me.lstCompanyName.RowSource = "select companyname,businesstype from contacts
where contact.BusinessType = " & [MeBusinessType.Value]
Me.lstCompanyName.Requery
end sub
The result within the listbox is the whole string I use, instead of the
required companyname.

What went wrong?

Greetings,
Harry
 
Try - Me.BusinessType
Instead of - [MeBusinessType.Value]


Private Sub ComboBusinessType_AfterUpdate()
Me.lstCompanyName.RowSource = "select companyname,businesstype from contacts
where contact.BusinessType = " & Me.BusinessType
Me.lstCompanyName.Requery
end sub
 
Hi Ofer,
Thanks for the reply.
However, I cann't get it to work.

The combobox returns a value, corresponding with the field
contacts.businesstype
When I uses the string, it surprises me that values writing down in
lowercase is not auto-corrected to uppercase (fe. businesstype >>
BusinessType).
When I run the query the debugger prompts at the WHERE command.
saying sub or function not defined.

The form is not bound to any table, and the lstbox is set to valuelist.
changing the form and/or lstbox properties does not give any improvement.
I have used the query as
Private Sub ComboBusinessType_AfterUpdate()
Me.lstCompanyName.RowSource = "select companyname,businesstype from contacts
where contacts.BusinessType = " & Me.BusinessType
Me.lstCompanyName.Requery
end sub


Ofer Cohen said:
Try - Me.BusinessType
Instead of - [MeBusinessType.Value]


Private Sub ComboBusinessType_AfterUpdate()
Me.lstCompanyName.RowSource = "select companyname,businesstype from contacts
where contact.BusinessType = " & Me.BusinessType
Me.lstCompanyName.Requery
end sub

--
Good Luck
BS"D


Harry said:
Hi All,

Within my db I have a table containing BusinesstypeID and BusinessName.
I have created a form with a combobox wherein I can select the BusinessName
and the BusinessID is the result. (checked it with a msgbox).
Now within the form I have created a listbox in which I want to display all
CompanyNames ( from the table Contacts) having this BusinesstypeID.
The vba code used
Private Sub ComboBusinessType_AfterUpdate()
Me.lstCompanyName.RowSource = "select companyname,businesstype from contacts
where contact.BusinessType = " & [MeBusinessType.Value]
Me.lstCompanyName.Requery
end sub
The result within the listbox is the whole string I use, instead of the
required companyname.

What went wrong?

Greetings,
Harry
Click to expand...
Click to expand...
 
Hi Ofer,
It did work!!!
I became someway confused but after putting the whole string into just 1
line it worked fine.
Thanks
 
Back
Top