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:
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:
...and a button (buttonSearch), with an OnClick event:
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
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
section will work fine and I've used it elsewhere.
Then the selecting the right record arguments part
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?
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:
Code:
[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:
Code:
SELECT [Surname] &", "& [Forename], [Student ID], [Postgrad] FROM qry_DynamicSearch_NameSearch;
...and a button (buttonSearch), with an OnClick event:
Code:
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
Code:
Me.combo_NameSearch.Column(2)
The
Code:
Then DoCmd.OpenForm "frm_Students_UG"
Then the selecting the right record arguments part
Code:
, , , "[Student ID] = """ & Me![Student ID] & """"
Hopefully I provided enough for some of the many wiser heads than me to go on?
Last edited: