VBA referencing one field of several in an SQL driven combo box (1 Viewer)

[Kelanen]

Hi all, As is usual here, I have a problem! In fact I have two, but we'll look at the more pressing for now.

I'm not a programmer. Just for background, I've been working with Access on and off since Access 95, although until 6 months ago hadn't touched anything later than 2000. This all relates to a problem in Access 2010, although I doubt the version is relevant here, I think it's me just not understanding the basics of referencing things in VBA.

VBA is very new to me, (as in the last 6-8 weeks). Prior to that the last coding I did was back at uni 20 years ago on things like Basic and Pascal (predating the Object Oriented revolution entirely). I've always said that I can do pretty much any Access work anyone requires as long as it doesn't need coding or SQL, but here I am working on some databases that have needed coding so I'm kind of learning as I go with copious use of Google, and forums like this one. So far I've muddled through by trial and error, and copying examples, and got it do what I need to do, but at the moment I am stuck - and I'm sure it's something very basic!

I have a form with a simple query control source containing 4 fields:

[Student ID] type Text
[Forename] type Text
[Surname] type Text
[PostGrad] type Yes/No

The form (actually it's a subForm, but don't worry about that - this is all being done in isolation in just the subForm) contains two controls:

An unbound combo box (combo_NameSearch) which which is populated by the following SQL:

SELECT [Surname] &", "& [Forename], [Student ID], [Postgrad] FROM qry_DynamicSearch_NameSearch;

...and a button (buttonSearch), with an OnClick event:

Private Sub button_Search_Click()
 
'Check whether UG/PG and open appropriate form
If Me.combo_NameSearch.Column(2) = "False" Then DoCmd.OpenForm "frm_Students_UG", , , "[Student ID] = """ & Me![Student ID] & """"
If Me.combo_NameSearch.Column(2) = "True" Then DoCmd.OpenForm "frm_Students_PG", , , "[Student ID] = """ & Me![Student ID] & """"
 
End Sub

The unbound dropdown works just fine. I've added the PostGrad field, and an extra column and visibility as part of troubleshooting, although I'm sure that it would work without displaying it, and probably not including in the combo box at all. (Any answer on that welcome, but I can easily trial and error it when the rest is working).

The problem is the VBA behind the button click - currently it does nothing. Intriguingly, it doesn't even error and open the debugger, which earlier pieces of code did. So I'm guessing that my code is now 'legal', just not doing what I want.

So what do I want? I want the user to be able to select a student from the list in the dropdown, and when you click on the button, to have it open a form with their details in. The slight catch is that Undergraduate (UG) and Postgraduate (PG) students have very different information and need to be displayed in different forms. For the rest of the database you are looking at groups of students, and they would never be a mix of the two groups -

Elsewhere I've already written some code that selects fields from a dynamic search front end, picks the right form and displays it and that's fine. That does it by storing the search criteria in a temporary table, and using those fields as the query criteria behind the subsequent form. It may be that that is the approach I should be using here, in which case I need to know how to unpick 4 fields from an SQL statement in an unbound text box to write them to a table - the rest I can do.

What I have actually tried to do here though, which seems to my ignorant mind much simpler - since I just want a single student rather than a dynaset with 6 filters, I thought it would be easy enough to test one condition in the combo box ([PostGrad] = Yes or No?) and then open a form containing that record (specified by [Student ID]). Is that conceptually possible, or am I just barking up the wrong tree and trying to use an impossible method?

Hovering over the

Me.combo_NameSearch.Column(2)

section in debug mode confirms I am definitely referencing the right field. It's a Yes/No in the table, although I notice it displays as True/False in a text box, and hovering over the code shows the current field having a value of -1(and presumably 0). I've tried all three of those, but to no avail, although I suspect all of them work? This section has a possible error?

The

Then DoCmd.OpenForm "frm_Students_UG"

section will work fine and I've used it elsewhere.

Then the selecting the right record arguments part

, , , "[Student ID] = """ & Me![Student ID] & """"

I've copied this from help sites, but I may have implemented it wrong? I've variously seen """ and "'" suggested for a text field, but the latter gets a space added and interpreted as a comment automatically, hence I have it the former way. This section has a probable error.

Hopefully I provided enough for some of the many wiser heads than me to go on?

 

[pr2-eugin]

Kelanen, Welcome to AWF..

You mentioned that the Field PostGrad is Yes/No type.. So they are not String.. Which you have compared against.. If = "True" , So try the following code..

Private Sub button_Search_Click()
    'Check whether UG/PG and open appropriate form
    If Me.combo_NameSearch.Column(2) Then 
        DoCmd.OpenForm "frm_Students_PG", , , "[Student ID] = '" & Me![Student ID] & "'"
    Else
        DoCmd.OpenForm "frm_Students_UG", , , "[Student ID] = '" & Me![Student ID] & "'"
    End If
End Sub

Hopefully I provided enough for some of the many wiser heads than me to go on?

A lot..

 

[Kelanen]

You mentioned that the Field PostGrad is Yes/No type.. So they are not String.. Which you have compared against.. If = "True" , So try the following code..

Private Sub button_Search_Click()
    'Check whether UG/PG and open appropriate form
    If Me.combo_NameSearch.Column(2) Then 
        DoCmd.OpenForm "frm_Students_PG", , , "[Student ID] = '" & Me![Student ID] & "'"
    Else
        DoCmd.OpenForm "frm_Students_UG", , , "[Student ID] = '" & Me![Student ID] & "'"
    End If
End Sub

I am probably being thick here... but surely that code doesn't actually test against column 2? Where does it test for a yes/no? (unless that is somehow implicit in the If...Then...Else Structure?)

Shouldn't it be something like

If Me.combo_NameSearch.Column(2) [B]SomeTestSyntaxHere[/B] Then

Thanks!

 

[pr2-eugin]

Since the Field is Yes/No type, the data in that column will be either True/False Or Yes/No Or -1/0 they are all the same.. With If statements, the operation is

If <condition is True> Then

The condition is Me.combo_NameSearch.Column(2), whihc will as mentioned will have only True/False.. There is nothing wrong with..

If Me.combo_NameSearch.Column(2) Then
If Me.combo_NameSearch.Column(2) = True Then
If Me.combo_NameSearch.Column(2) = Yes Then
If Me.combo_NameSearch.Column(2) = -1 Then

They are all the same and will provide the same result..

 

[Kelanen]

I was just coming back to say that your code did indeed do the trick - I just didn't understand why it did!

But thank you, the explanation above is very clear.

 

[pr2-eugin]

That's good to hear.. Good luck..

 

[Kelanen]

Actually.. it's not quite working? It's damn close, but not quite.

What it's doing is opening the correct form, based on the student that was selected, but it's then always populating that form with the first student in the list, not the one that was actually selected.

Any idea?

 

[pr2-eugin]

How about this..

Private Sub button_Search_Click()
    'Check whether UG/PG and open appropriate form
    If Me.combo_NameSearch.Column(2) Then 
        DoCmd.OpenForm "frm_Students_PG", , , "[Student ID] = '" & [B]Me.combo_NameSearch.Column(1)[/B] & "'"
    Else
        DoCmd.OpenForm "frm_Students_UG", , , "[Student ID] = '" & [B]Me.combo_NameSearch.Column(1)[/B] & "'"
    End If
End Sub

 

[Kelanen]

That works perfectly, thank you!

The perils of using Me!...