Combo box not populating 2

[Trowser]

Ok I have a series of combo boxes and I want each combo box to display the data depending on whats in the other combo boxes. but I cannot get the combo box to populate any data I get no errors and even when I put a break on the event procedure nothing happens.

Option Compare Database
Dim Fname As String
Dim sname As String
Dim Pay As String
Fname = cboFname.Text
sname = cboSname.Text
Pay = cboPayroll.Text

Private Sub cboPayroll_Click()

If Fname + sname = "" Then
cboPayroll.RowSource = "SELECT UserDetails.Payroll FROM UserDetails;"
ElseIf Fname = Not "" And sname = "" Then
cboPayroll.RowSource = "SELECT UserDetails.Payroll, UserDetails.[First Name] FROM UserDetails WHERE (((UserDetails.[First Name])=" + Fname + &quot);"
ElseIf sname = Not "" And Fname = "" Then
cboPayroll.RowSource = "SELECT UserDetails.Payroll, UserDetails.Surname FROM UserDetails WHERE (((UserDetails.Surname)=" + sname + &quot);"
ElseIf sname = Not "" And Fname = Not "" Then
cboPayroll.RowSource = "SELECT UserDetails.[First Name], UserDetails.Surname, UserDetails.Payroll FROM UserDetails WHERE (((UserDetails.[First Name])=" + Fname + &quot AND ((UserDetails.Surname)=" + sname + &quot);"
End If
Form.Refresh
cboPayroll.Requery
End Sub


Thats the code hope ppl understand it ok and can tell me why it aint doing what I thought it should

 

[MakeItSo]

You can do this easier:

Comment out your entire If-block and use this instead:
[blue]
cboPayroll.RowSource = "SELECT Payroll, [First Name], Surname FROM UserDetails " & _
"WHERE ([First Name] LIKE '" & Fname & "*' " & _
"AND Surname LIKE '" & sname & "*');"

cboPayroll.Requery
[/blue]

This should return values, no matter if fname and/or sname are empty. ;-)

Hope this helps,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[RoyVidar]

To add to MakeItSo's suggestion:

1 - place the declarations and assigning of values inside the click event
2 - if any of the controls are/might be null, it will error, because strings cannot be Null, only variants - change to variants, or perform isnull checks prior to assigning (or use form references directly)
3 - use the combos .value property. The .text property needs for the control to have focus.

That said, you might be lucky just using the control references within MakeItSo's code sample:

[tt]"WHERE ([First Name] LIKE '" & Me!cboFname.Value & "*' " & _
"AND Surname LIKE '" & Me!cboFname.Value & "*');"[/tt]

Roy-Vidar

 

[MakeItSo]

Thanks for the additions - important issues, Roy.

 

[RoyVidar]

Thanx, it's probably just a matter of which part of the code we start looking at - and of course I've used the same combo both places - need new glasses (or fingers, or brain;-))...

- additional tips, use the 'Option Explicit' option at the top of the module (add it below 'Option Compare Database'), it enforces variable declaration (in VBE Tools | Options, also check the "Require Variable declaration" check box, so new modules have this as default)

Roy-Vidar

 

[Trowser]

Ok thanks for the help lads I recognised some things you mentioned but been 2 yrs since I last had to look at VBA or VB so still trying to kick in my memory

Private Sub cboPayroll_Click()
Dim fname As String
Dim sname As String
fname = cboFname.Value
sname = cboSname.Value
If sname Is Null Then sname = ""
If fname Is Null Then fname = ""
cboPayroll.RowSource = "SELECT Payroll, [First Name], Surname FROM UserDetails " & _
"WHERE ([First Name] LIKE '" & fname & "*' " & _
"AND Surname LIKE '" & sname & "*');"

cboPayroll.Requery
End Sub

ok changed it to look like the above and still no result
FYI - on the cbo control the Row Source and row source type are blank
I remember doing this many times in VB with no problems (can't remember how I did it) so I know its prolly something stupid I am missing out

 

[RoyVidar]

See my above comments. Either use variant datatype or check for null prior to assigning the values. Checking for null, use the IsNull funtion.

BUT in stead of using variables etc, why don't you try out MakeItSo's suggestion, perhaps with my modifications (combo references and drop the variables).

Roy-Vidar

 

[RoyVidar]

Perhaps also ensure that the event fires. Set a breakpoint on one of the lines (F9) and check it.

Also, perhaps have a look at which event you'll fire it on. Off the top of the head, perhaps the on got focus event of the combo? (Not sure what triggers a combos on click event)

Roy-Vidar

 

[Trowser]

Ok thanks Roy and make it so

I tried loads of differant ways and both of your suggestions (Combo fires on focus) and well I found the problem why it wasn't populating and that was because the first 2 combo boxes were returning garbage

Thanks for the help though

 

[MakeItSo]

Ahhhh - bound to wrong column I bet. Haven't thought of that.
Keep rockin!

 

[Trowser]

ok still have a problem with this for some reason

CostCentre.ControlSource = "SELECT Departments.[Cost Center], Team.Team" & _
" FROM Departments INNER JOIN Team ON Departments.[Service Manager] = Team.[Service Manager] " & _
"WHERE (((Team.Team) Like " & cboDept.Value & &quot);"
CostCentre.Requery


I get control cannot be edited it is bound to an unknown Field and all I get in the textbox is #Name?