Combo Box and SubForm

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nanette - 13 Mar 2007 18:54 GMT

I have a combo box that lists BOE ID numbers on a form (frmPricing). Once the
user selects the choice, I want a subform (sfrmPricingA) to fill in
automatically.

My code is:

Private Sub cboBOENo_AfterUpdate()
sSQL = "SELECT * FROM BOE WHERE " & _
"id_BOE = " & Chr(39) & Me!cboBOENo & Chr(39)

Me!sfrmPricingA.Form.RecordSource = sSQL

End Sub

I get an error message that "You canceled the previous operation". When I
hit the debug button the "Me!sfrmPricingA.Form.RecordSource = sSQL" is
highlighted in yellow.

How do I get this to work?

Reply to this Message

Carl Rapson - 14 Mar 2007 17:00 GMT

Put a breakpoint on the line where the error occurs, and at the break in the
Immediate window enter "? sSQL". Paste the resulting SQL code that's
displayed into the SQL View of a new (blank) query and see if you get the
results you expect. If an error occurs, it might have enough informaiton for
you to track down the error.

Carl Rapson

>I have a combo box that lists BOE ID numbers on a form (frmPricing). Once
>the
[quoted text clipped - 16 lines]
>
> How do I get this to work?

Reply to this Message

Nanette - 14 Mar 2007 17:20 GMT

Thanks for the suggestions Carl!

> Put a breakpoint on the line where the error occurs, and at the break in the
> Immediate window enter "? sSQL". Paste the resulting SQL code that's
[quoted text clipped - 24 lines]
> >
> > How do I get this to work?

Reply to this Message

Nanette - 14 Mar 2007 18:06 GMT

Hi Carl,

I'm very new to VBA and thought I could figure out what you said below, but
I'm having problems with it. So my questions are:

Is a breakpoint that brownish red dot that appears on the side of the Visual
Basic code when you click there?

I put the brownish red dot next to the code that was highlighted
automatically by VBA, and then put "? sSQL" in the immediate window. I then
hit the return key and got the following:

Compile Error: expecting line number or label or statement or end of
statement.

Sorry to say that I don't understand what the compile error means.

Am I doing this correctly? If not, could you explain in more detail? If I'm
doing this correctly, could you explain where I should look for answers to
the compile error statement?

Reply to this Message

Carl Rapson - 15 Mar 2007 17:01 GMT

Yes, that is the breakpoint. When you run your code, it will "break" at that
line (the line will turn yellow) and pause. When the code execution breaks
at the breakpoint, open the immediate window by pressing Ctrl+G (or select
Immediate Window from the View menu at the top of the Code window).

So here are the steps:

1. Set the breakpoint.

2. Run the form and make a selection in the combo box.

3. When execution pauses, go to the Code window and open the Immediate
Window as described above.

4. Type "? sSQL" (without the quotes) in the Immediate Window and press
Enter.

5. Copy the resulting SQL code (highlight it and press Ctrl+C).

6. From the Database screen, open a new Query, select Design View, close the
Show Table window (don't select a table), and then switch to SQL View
(should be in the upper left of the screen, just below the File menu).

7. Delete whatever is in the SQL View window and paste your SQL code into
the window with Ctrl+V.

8. by Execute the SQL code by clicking the reddish-brown exclamation point
in the tookbar (or select Run from the Query menu).

If there is a problem in the SQL code, you should get an error. The error
might give you enough information to make a determination as to the problem.

Carl Rapson

> Hi Carl,
>
[quoted text clipped - 53 lines]
>> >
>> > How do I get this to work?

Reply to this Message

Nanette - 15 Mar 2007 21:27 GMT

Thanks Carl!

I'm gonna give it a try! I'll let you know how it goes.

> Yes, that is the breakpoint. When you run your code, it will "break" at that
> line (the line will turn yellow) and pause. When the code execution breaks
[quoted text clipped - 87 lines]
> >> >
> >> > How do I get this to work?

Reply to this Message