Access 2010 first macro - errors

[DLParker]

I'm having trouble setting up my first macro.



I'm trying to create a form where certain controls are displayed or hidden based on a value in another control. Access's help file suggested I should use a macro if possible. After scanning through a few books and web pages I thought I had the right structure:

Users will select one of the following values: "LOCAL"; "MORE"; "OCLC"; "UNFILLED" from a combo box named LoanType. The control LocalLender has its Visible property set at No to start with. I want it to change to Yes when LOCAL is selected from the list. Both fields are text fields.

I clicked in the On Change property for this combo box and chose the Macro Builder. This is the expression:

IF [Forms]![ILL Requests Form]![LoanType]="LOCAL" Then

SetProperty

Control Name [Forms]![ILL Requests Form]![LocalLender]

Property Visible

Value Yes

When I try to run this macro, Access gives me a Type Mismatch error. Once I get this part working, I want to make other controls visible when other values are chosen from the LoanType box. I would appreciate any help you can give!

[June7]

What is the RowSource of the combobox? I suspect it is multi-column and value is actually a record ID, not the text descripter.

I use VBA. I would select [Event Procedure] in the event property, double click the ellipses (...), this will put you in the procedure. Then code would be like:

Select Case Me.LoanType
Case 1
Me.LocalLender.Visible
Case 2
...
End Select

[DLParker]

The Row Source Type for the combo box is a Value List and the Row Source itself is "LOCAL";"MORE";"OCLC";"UNFILLED"

I'm very new to VBA. When you suggest the solution

Select Case Me.LoanType
Case 1
Me.LocalLender.Visible
Case 2
...
End Select

can you give me an example of how to write a "case 1" expression? would that line simply read

"LOCAL"

or

= "LOCAL" or what?

Thanks!

[June7]

So the combobox is not multi-column and LoanType field is text datatype? Don't understand why the macro errors.

Put in place of 1, 2 whatever value you want to match, no equal sign required, like: Case "Local".

[DLParker]

So the combobox is not multi-column and LoanType field is text datatype? Don't understand why the macro errors.

Yeah, me either.

Okay, here's what I have now:

Private Sub RequestSource_AfterUpdate()
Select Case Me.LoanType
Case "LOCAL"
Me.LocalLender.Visible
Case "OCLC"
Me.ILLNo.Visible
Case "MORE"
Me.MOREno.Visible
End Select
End Sub

and I get a "Compile Error Invalid Use of Property" with the .Visible of Me.LocalLender.Visible highlighted. Any further insight? Is there a problem placing this code in the After Update property? Or is the Visible property not set-able this way? Thanks again.

[June7]

This is possible. I have done it. Is LocalLender the name of a textbox or combobox or listbox on the form? I always give data controls a name different from the field they are bound to, like: txtLocalLender or cbxLocalLender or lbxLocalLender.

[DLParker]

Many, many thanks for the good advice. I have a working event procedure! This is what I ended up with:

Select Case Me.cbxLoanType
Case "LOCAL"
Me.cbxLocalLender.Visible = True
Me.lblLocalLender.Visible = True
Case "MORE"
Me.cbxMORENum.Visible = True
Me.lblMORENum.Visible = True
Case "OCLC"
Me.cbxOCLCNum.Visible = True
Me.lblOCLCnum.Visible = True
End Select

Now my boxes and their labels appear only when needed. And you're right: giving the various controls names that distinguish them from their bound fields, similarly-named objects, etc., helps keep them straight.

[June7]

If the label control is associated with data control, no need for separate code, it will do what the data control does.

[DLParker]

For whatever weird reason, my labels stay visible unless I specifically hide them. But otherwise everything works!

[June7]

I just just tested VBA that sets textbox not Visible and its associated label also becomes not visible. Select the data control, if the label is associated it will also be selected and move with the control. You have a solution, so this is just a matter of curiosity if you want to pursue.