Dynamic SQL - Missing Parameter (1 Viewer)

The Archn00b · Dec 3, 2013

I've attempted to create a form with a dynamic subform. Records for the subform are filtered using check boxes which supplies parameters to the Visual Basic script (below). The VB Script changes the Record Source (SQL) of the subform.

Checkbox variables are defined/declared:

Code:

Private Sub Check1_Click()
Dim Check1Value As Boolean
Check1Value = Check1.Value
End Sub

Private Sub Check2_Click()
Dim Check2Value As Boolean
Check2Value = Check2.Value
End Sub

Then the SQL for the Record Source is created:

Private Sub Submit_Button_Click()

Code:

Dim Parameter1 As String
If Check1Value = "True" Then
Parameter1 = "Member_types = 1"
Else
Parameter1 = ""
End If

Dim Parameter2 As String
If Check2Value = "True" Then
If Check2Value = "" Then
Parameter2 = "Member_types = 2"
Else
Parameter2 = "OR Member_types = 2"
End If
Else
Parameter2 = ""
End If

Dim Record_Source As String
Record_Source = "SELECT * FROM Query WHERE Check1Value & Check2Value"

Forms!Form!Subform.Form.RecordSource = Record_Source

End Sub

Access asks me to input values for Parameters 1 and 2. I type in the values that I want and the code, works fine. However the problem is it is not finding values for Parameters 1 and 2.

Any ideas?

Thanks for reading!

:banghead:

pbaldy · Dec 3, 2013

Check1Value and Check2Value are declared within Sub's, so are only available within those Sub's. They either need to be declared at the top of the form module or in a standard module.

namliam · Dec 3, 2013

if you want to replace in variables you do that OUTSIDE the text string which is enclosed by ""

Thus.... for example without looking beyond the immediate issue.....
Record_Source = "SELECT * FROM Query WHERE " AdultParameter & FamilyParameter & FounderParameter & WatchParameter & BusinessParameter

The Archn00b · Dec 3, 2013

pbaldy said:
Check1Value and Check2Value are declared within Sub's, so are only available within those Sub's. They either need to be declared at the top of the form module or in a standard module.

Ah, so they need to start with Public Sub Check1_Click()?

pbaldy · Dec 3, 2013

No, these lines need to be above the first sub in the form module rather than inside a sub:

Dim Check1Value As Boolean
Dim Check2Value As Boolean

The Archn00b · Dec 3, 2013

pbaldy said:
No, these lines need to be above the first sub in the form module rather than inside a sub:

Dim Check1Value As Boolean
Dim Check2Value As Boolean

Code:

Dim Check1Value As Boolean
Dim Check2Value As Boolean

Private Sub Check1_Click()
Check1Value = Check1.Value
End Sub

Private Sub Check2_Click()
Check2Value = Check2.Value
End Sub

Like this?

pbaldy · Dec 3, 2013

Yes, that should make them available from any code in that form.

The Archn00b · Dec 4, 2013

pbaldy said:
Yes, that should make them available from any code in that form.

Still not recognizing a value for the parameters.

namliam · Dec 4, 2013

namliam said:
if you want to replace in variables you do that OUTSIDE the text string which is enclosed by ""

Thus.... for example without looking beyond the immediate issue.....
Record_Source = "SELECT * FROM Query WHERE " AdultParameter & FamilyParameter & FounderParameter & WatchParameter & BusinessParameter

Moreover you are filling Parameter1 and parameter2, but not using them in your SQL.....

:banghead:

The Archn00b · Dec 4, 2013

namliam said:
if you want to replace in variables you do that OUTSIDE the text string which is enclosed by ""

Thus.... for example without looking beyond the immediate issue.....
Record_Source = "SELECT * FROM Query WHERE " AdultParameter & FamilyParameter & FounderParameter & WatchParameter & BusinessParameter

OK so

Code:

Record_Source = "SELECT * FROM Query WHERE " & AdultParameter & FamilyParameter & FounderParameter & WatchParameter & BusinessParameter & ";"

But if I do this, I get an end of statement error. Is it a different type of quotation mark/enclosing symbol other than "" used?

Moreover you are filling Parameter1 and parameter2, but not using them in your SQL.....

Yes lol. It's a problem with the example I've submitted, but not my actual code. Don't worry about that. I'll edit my original post.

The Archn00b · Dec 4, 2013

Figured it out. Thanks for pointing me in the right direction.

namliam · Dec 4, 2013

So what did you end up fixing? Just for curiousity sake

The Archn00b · Dec 4, 2013

I changed the way I was adding variables into the SQL string similar to the way you said, then I changed the position of the first Dims to be in the same Sub routine as the main code, finally I found that one of my SQL variables was incorrect for my database.

Huge relief. That was the last piece of the puzzle for a major project for me.

The Archn00b · Dec 5, 2013

OK, so I got my code working but now I've hit another snag. As mentioned, I'm using SQL to create a dynamic SQL query. So something like:

Code:

SELECT * WHERE (FieldA = 1 OR FieldA = 2) AND (FieldB = 1 OR FieldB = 2);

I'm now coding the

Code:

AND (FieldB = 1 OR FieldB = 2)

FYI Each of the Field values are saved as Dim variables.

I know how to do it, but I need to be able to use Else If, which is creating an error message.

Code:

Dim FieldBParameter As String
If CheckboxValue = "True" Then
     If  FieldAParameters = "" And 1stFieldBParameter = "" Then
     FieldBParameter = "FieldB = 1"
     Else If FieldAParameters <> "" And FieldAParameters = "" Then
     FieldBParameter = "AND Payment_Type = 1"
     Else 
     FieldBParameter = "OR Payment_Type = 1"
     End If
Else 
FieldBParameter = ""
End If

It's the first Else IF that is causing the problem. In the VB editor it is highlighted in red and I get the error message:

Compile Error: Must be the first statement on the line

Any ideas anyone?

Thanks!

The Archn00b · Dec 5, 2013

GODAMMIT I just figured it out. It's not "Else If" it's "ElseIf"

I'll stop being such a newb someday I promise.

namliam · Dec 5, 2013

ElseIf not Else If

Dynamic SQL - Missing Parameter (1 Viewer)

The Archn00b

Registered User.

pbaldy

Wino Moderator

namliam

The Mailman - AWF VIP

The Archn00b

Registered User.

pbaldy

Wino Moderator

The Archn00b

Registered User.

pbaldy

Wino Moderator

The Archn00b

Registered User.

namliam

The Mailman - AWF VIP

The Archn00b

Registered User.

The Archn00b

Registered User.

namliam

The Mailman - AWF VIP

The Archn00b

Registered User.

The Archn00b

Registered User.

The Archn00b

Registered User.

namliam

The Mailman - AWF VIP

Similar threads

Users who are viewing this thread