Newbie Combo / Form question

G

Garfield

Hello

I have a table called users, and there are 3 types of users, Client,
Counsellors & Volunteers
My switchboard has options to view each type.
I am using same form to display the information of each user type.

I have a combo box which shows the correct users dependant on the user type
selected to display.
However, when the form is first loaded, the combo box is correct,
but the form is showing the 1st record of the table and not of the user type
selected.
Only after I select a value in the combo box will the form synch with the
combo box.

My question is:
How do I get the form to be in synch with the combo when the form is first
opened.

Here is my code so far

Private Sub Form_Load()
Me.cboUserList.RowSourceType = "Table/Query"

Select Case strDisplayUserType
Case "Counsellors"
Me.btnTest.Caption = "Show all Counsellors"
Me.cboUserList.RowSource = "qryAllCounsellors"
Case "Clients"
Me.btnTest.Caption = "Show all Clients"
Me.cboUserList.RowSource = "qryAllClients"
Case "Volunteers"
Me.btnTest.Caption = "Show all Volunteers"
Me.cboUserList.RowSource = "qryAllVolunteers"
Case Else
Me.btnTest.Caption = "Show all Users"
Me.cboUserList.RowSource = "qryAllUsers"
End Select

Forms!Users!cboUserList.Requery 'reloads the combo
box list to reflect the Query
Me!cboUserList = Me!cboUserList.ItemData(0) 'set the first entry in
the combo box

End Sub

Private Sub cboUserList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[UserID] = " & Str(Nz(Me![cboUserList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks
Garfield
 
N

Neil

Hello Garfield,

From the looks of your code, you only update the combo in the OnLoad event
of your form. Try calling the AfterUpdate procedure from the Load event as
follows:

Private Sub Form_Load()
Me.cboUserList.RowSourceType = "Table/Query"

Select Case strDisplayUserType
Case "Counsellors"
Me.btnTest.Caption = "Show all Counsellors"
Me.cboUserList.RowSource = "qryAllCounsellors"
Case "Clients"
Me.btnTest.Caption = "Show all Clients"
Me.cboUserList.RowSource = "qryAllClients"
Case "Volunteers"
Me.btnTest.Caption = "Show all Volunteers"
Me.cboUserList.RowSource = "qryAllVolunteers"
Case Else
Me.btnTest.Caption = "Show all Users"
Me.cboUserList.RowSource = "qryAllUsers"
End Select

Forms!Users!cboUserList.Requery 'reloads the combo
box list to reflect the Query
Me!cboUserList = Me!cboUserList.ItemData(0) 'set the first entry in
the combo box

' Call Combobox After Update to synchronise
Call cboUserList_AfterUpdate

End Sub

That should do the trick

HTH,

Neil.
 
G

Garfield

Hi Neil,

After much deliberation and pulling of the litle hair I have, I solved it.

I created a recordset based on a SELECT statement for the strDisplayUserType

Then set the Me.RecordSource to my new Recordset.Name

And that worked.

Thanks for looking.

Garfield

Neil said:
Hello Garfield,

From the looks of your code, you only update the combo in the OnLoad event
of your form. Try calling the AfterUpdate procedure from the Load event as
follows:

Private Sub Form_Load()
Me.cboUserList.RowSourceType = "Table/Query"

Select Case strDisplayUserType
Case "Counsellors"
Me.btnTest.Caption = "Show all Counsellors"
Me.cboUserList.RowSource = "qryAllCounsellors"
Case "Clients"
Me.btnTest.Caption = "Show all Clients"
Me.cboUserList.RowSource = "qryAllClients"
Case "Volunteers"
Me.btnTest.Caption = "Show all Volunteers"
Me.cboUserList.RowSource = "qryAllVolunteers"
Case Else
Me.btnTest.Caption = "Show all Users"
Me.cboUserList.RowSource = "qryAllUsers"
End Select

Forms!Users!cboUserList.Requery 'reloads the combo
box list to reflect the Query
Me!cboUserList = Me!cboUserList.ItemData(0) 'set the first entry in
the combo box

' Call Combobox After Update to synchronise
Call cboUserList_AfterUpdate

End Sub

That should do the trick

HTH,

Neil.

Garfield said:
Hello

I have a table called users, and there are 3 types of users, Client,
Counsellors & Volunteers
My switchboard has options to view each type.
I am using same form to display the information of each user type.

I have a combo box which shows the correct users dependant on the user type
selected to display.
However, when the form is first loaded, the combo box is correct,
but the form is showing the 1st record of the table and not of the user type
selected.
Only after I select a value in the combo box will the form synch with the
combo box.

My question is:
How do I get the form to be in synch with the combo when the form is first
opened.

Here is my code so far

Private Sub Form_Load()
Me.cboUserList.RowSourceType = "Table/Query"

Select Case strDisplayUserType
Case "Counsellors"
Me.btnTest.Caption = "Show all Counsellors"
Me.cboUserList.RowSource = "qryAllCounsellors"
Case "Clients"
Me.btnTest.Caption = "Show all Clients"
Me.cboUserList.RowSource = "qryAllClients"
Case "Volunteers"
Me.btnTest.Caption = "Show all Volunteers"
Me.cboUserList.RowSource = "qryAllVolunteers"
Case Else
Me.btnTest.Caption = "Show all Users"
Me.cboUserList.RowSource = "qryAllUsers"
End Select

Forms!Users!cboUserList.Requery 'reloads the combo
box list to reflect the Query
Me!cboUserList = Me!cboUserList.ItemData(0) 'set the first entry in
the combo box

End Sub

Private Sub cboUserList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[UserID] = " & Str(Nz(Me![cboUserList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks
Garfield
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Synchronizing Form and ComboBox 1
Hlp! Requery Cbo Not Working 3
User form Text Box & Combo Box. 5
Cancel/BeforeUpdate Event Procedure 1
Selecting from Worksheet to fill combo boxes on User Form 1
how to update a record from combo-box in user form? 0
Combo Box Adding Button to edit list items 5
Prevent duplicate selection from combo box on continuous form 4

Top