Refreshing a subform based on selections in a combo box

  • Thread starter Thread starter Dan S.
  • Start date Start date
D

Dan S.

I have a form with a combo box, then a subform. I'm
wanting the results in the subform to change based on
selections in the combo box. I don't know VB, and was
wondering if there was a way to accomplish this. Any help
will be greatly appreciated.


Thanks,

D.
 
There is definately a way to accomplish this but I only know how to do so
with VBA. I suppose a macro would work but I never really learned macros -
went straight to VBA.

By your wording are you looking for a way to do this without VBA or are you
willing to learn and use VBA?

Tony
 
I thing you'll be hard pressed to fing a viable non-VBA method. For what it
is worth, if you are going to be working on or developing Access
applications for any period of time you are going to need to learn VBA.
There are tons of great references out there for Access in general that
included tons of VBA. There is no need to reinvent the wheel - a lot of what
you will want to do someone has already done it and you just need to find an
example. most people are very willing to share their code. Here are a few
links...

http://www.granite.ab.ca/accsmstr.htm
http://www.developershandbook.com/
http://www.mvps.org/access/index.html

But back to your question. Below is code I used on one of my forms. I saved
my message before sending to take care of word wrap but watch for it anyway.

This is in the after update event of the combo box. The combo box itself has
a rowsource that pulls the exact criteria I am using to fill in the subform.
There is a lot going on here for someone who has not used VBA. Since you are
trying to avoid VBA I am guessing this may confuse you more then help you
but who knows. I will try to explain each line with comments before each
line. Comments can be added to code by simply prefacing each comment with a
single quote '. In my example you can remove all the comment and blank lines

If you don't know how to start a VBA event procedure - open the main form in
design view, view the properties of the combo box. Go to the Event tab. In
the drop down next to After Update select [Even Procedure], then click the
button with the ... on it. Copy and paste everything below between the
astericks into your event procedure. Color coding done by Access will make
it much easier to read. After pasting you should remove my Private Sub...
and End Sub lines to prevent errors. If you get any red lines you may need
to simply hit delete at the end of the previous line to get everything on
the right line.

***start code***
'Beginning of subroutine, this line is added autoimatically by Access when
'creatting an event procedure
Private Sub cboLocationFilter_AfterUpdate()

'Tells VBA where to go (other then Hell that is) in the event of an error
'genericaly called error handling
On Error GoTo Error_Sub

'Declare a variable to hold info for use elswhere in code
'in this case a string
Dim sqlString as String

'Checks if the combo box is null is a zero-length string
'there are other methods but this works
If IsNull(Me.cboLocationFilter) Or Me.cboLocationFilter = "" Then
'combo box is null or blank so do the following
Me.Subform.Form.RecordSource = "qryBusinessClients_Employees"
Else 'combo box is not null or blank so do something else
'sets the value of the sqlString variable declared above
sqlString = "SELECT qryBusinessClients_Employees.* " _
& "FROM qryBusinessClients_Employees " _
& "WHERE (((qryBusinessClients_Employees.WorkSite)= " _
& Chr(34) & Me.cboLocationFilter _
& Chr(34) & ")) ORDER BY LastName;"

'Sets the record source of the subform the the sqlString varible
Me.Subform.Form.RecordSource = sqlString

'Nothing else to check the combo box for, so end the If statement
End If

'Exit if no errors of occured
Exit_Sub:
Exit Sub

'If errors occured do the following
Error_Sub:
'displays a message box with info about the error
MsgBox "Error: " & Err.Description & vbCr & "Error#: " & Err.Number, , _
"cboLocationFilter_AfterUpdate"

'after users click OK on the error message then continue back to the exit
'line
Resume Exit_Sub

'All done, end of subroutine, Access adds line automatically when creating
' the event procedure.
End Sub

***end code***

You will of course need to change the name of the combo box and subform
controls to the names you are using. And the sql string will need to be
changed to fit your database. If you wanted to you can use an existing query
for the record source and skip the whole sqlString variable if you like.

Lastly referring to main forms and subforms in code can get hairy. I use
this refernce religiously
http://www.mvps.org/access/forms/frm0031.htm

Sorry of this seems overwhelming. It took me a long time to really start
getting the idea of what was going on in code and I still only consider
myself a beginner/intermediate coder compared to the top notch folks in this
forum.

Hope this helps
Tony
 
Back
Top