subform returns only 1 record

G

Ginger

I have a form/subform that has 6 combo boxes and based on
the selection, the subform is updated accordingly. This was
working until I needed to add the field "ID".

Now with that field in the select statement, it only
returns the first record in the table. If I take out the
field "ID" from the select statement, I get "#Name?". Why
would it do this and what do I need to do to fix it?

Thank you in advance.

Ginger

Here is my statement:

strSQL = "Select [ID], [Make], [Model], [Year], [PartName],
[PartNumber], [RefNumber], [Condition], [PartDescription1],
[PartDescription2], [New], [Quantity], [Comment1],
[Comment2], [Comment3], [Sold], [DateSold], [From],
[Location], [3], [4], [5], [6], [7], [8], [9], [10] from
tblProduct where "
If Not IsNull(cboMake) Then
strSQL = strSQL & "[Make] = '" & cboMake & "'"
End If
If Not IsNull(cboModel) Then
strSQL = strSQL & " and [Model] = '" & cboModel & "' "
End If
If Not IsNull(cboYear) Then
strSQL = strSQL & " and [Year] = " & cboYear
End If
If Not IsNull(cboPartName) Then
strSQL = strSQL & " and [PartName] = '" &
cboPartName & "' "
End If
If Not IsNull(cboPartNumber) Then
strSQL = strSQL & " and [PartNumber] = '" &
cboPartNumber & "' "
End If
If Not IsNull(cboRefNumber) Then
strSQL = strSQL & " and [RefNumber] = '" &
cboRefNumber & "' "
End If

'set the subform to strSQL
subfrmProductbyMultipleItems.Form.RecordSource = strSQL
& " ORDER BY [Make] ASC, [Model] ASC, [Year] ASC,
[PartName] ASC, [PartNumber] ASC, [RefNumber] ASC,
[Condition] ASC, [PartDescription1] ASC, [PartDescription2]
ASC, [New], [Quantity] ASC, [Comment1] ASC, [Comment2] ASC,
[Comment3] ASC, [Sold] ASC, [DateSold] ASC, [From] ASC,
[Location], [3] ASC, [4] ASC, [5] ASC, [6] ASC, [7] ASC,
[8] ASC, [9] ASC, [10] ASC"
subfrmProductbyMultipleItems.Requery
 
G

Ginger

Perhaps I should have only posted the SQL select statement?

strSQL = "Select [ID], [Make], [Model], [Year], [PartName],
[PartNumber], [RefNumber], [Condition], [PartDescription1],
[PartDescription2], [New], [Quantity], [Comment1],
[Comment2], [Comment3], [Sold], [DateSold], [From],
[Location], [3], [4], [5], [6], [7], [8], [9], [10] from
tblProduct where "
Click to expand...

if statements

'set the subform to strSQL
subfrmProductbyMultipleItems.Form.RecordSource = strSQL
Click to expand...
-----Original Message-----
I have a form/subform that has 6 combo boxes and based on
the selection, the subform is updated accordingly. This was
working until I needed to add the field "ID".

Now with that field in the select statement, it only
returns the first record in the table. If I take out the
field "ID" from the select statement, I get "#Name?". Why
would it do this and what do I need to do to fix it?

Thank you in advance.

Ginger

Here is my statement:

strSQL = "Select [ID], [Make], [Model], [Year], [PartName],
[PartNumber], [RefNumber], [Condition], [PartDescription1],
[PartDescription2], [New], [Quantity], [Comment1],
[Comment2], [Comment3], [Sold], [DateSold], [From],
[Location], [3], [4], [5], [6], [7], [8], [9], [10] from
tblProduct where "
If Not IsNull(cboMake) Then
strSQL = strSQL & "[Make] = '" & cboMake & "'"
End If
If Not IsNull(cboModel) Then
strSQL = strSQL & " and [Model] = '" & cboModel & "' "
End If
If Not IsNull(cboYear) Then
strSQL = strSQL & " and [Year] = " & cboYear
End If
If Not IsNull(cboPartName) Then
strSQL = strSQL & " and [PartName] = '" &
cboPartName & "' "
End If
If Not IsNull(cboPartNumber) Then
strSQL = strSQL & " and [PartNumber] = '" &
cboPartNumber & "' "
End If
If Not IsNull(cboRefNumber) Then
strSQL = strSQL & " and [RefNumber] = '" &
cboRefNumber & "' "
End If

'set the subform to strSQL
subfrmProductbyMultipleItems.Form.RecordSource = strSQL
& " ORDER BY [Make] ASC, [Model] ASC, [Year] ASC,
[PartName] ASC, [PartNumber] ASC, [RefNumber] ASC,
[Condition] ASC, [PartDescription1] ASC, [PartDescription2]
ASC, [New], [Quantity] ASC, [Comment1] ASC, [Comment2] ASC,
[Comment3] ASC, [Sold] ASC, [DateSold] ASC, [From] ASC,
[Location], [3] ASC, [4] ASC, [5] ASC, [6] ASC, [7] ASC,
[8] ASC, [9] ASC, [10] ASC"
subfrmProductbyMultipleItems.Requery

.
Click to expand...
 
K

Ken Snell

Your result is puzzling. Just adding ID to the SELECT list should not cause
the situation that you're now observing.

Try pasting your SQL statement into a query and run it. Does it produce the
results that you wish? (You'll need to enter the "parameters" for the combo
box values when the query runs.)
 
G

Ginger

Thank you Ken.

I'll try that. I took ID out, and it ran fine, I put ID in
and it returns only one record. Back and forth, trying to
find a reason for it. So, I decided just to leave the ID
field out.

It worked fine on all the other pages that I added ID to
with no problem. It is just this one that does not want to
behave.

Once again, thank you for your help.

Ginger

-----Original Message-----
Your result is puzzling. Just adding ID to the SELECT list should not cause
the situation that you're now observing.

Try pasting your SQL statement into a query and run it. Does it produce the
results that you wish? (You'll need to enter the "parameters" for the combo
box values when the query runs.)

--
Ken Snell
<MS ACCESS MVP>

Ginger said:
I have a form/subform that has 6 combo boxes and based on
the selection, the subform is updated accordingly. This was
working until I needed to add the field "ID".

Now with that field in the select statement, it only
returns the first record in the table. If I take out the
field "ID" from the select statement, I get "#Name?". Why
would it do this and what do I need to do to fix it?

Thank you in advance.

Ginger

Here is my statement:

strSQL = "Select [ID], [Make], [Model], [Year], [PartName],
[PartNumber], [RefNumber], [Condition], [PartDescription1],
[PartDescription2], [New], [Quantity], [Comment1],
[Comment2], [Comment3], [Sold], [DateSold], [From],
[Location], [3], [4], [5], [6], [7], [8], [9], [10] from
tblProduct where "
If Not IsNull(cboMake) Then
strSQL = strSQL & "[Make] = '" & cboMake & "'"
End If
If Not IsNull(cboModel) Then
strSQL = strSQL & " and [Model] = '" & cboModel & "' "
End If
If Not IsNull(cboYear) Then
strSQL = strSQL & " and [Year] = " & cboYear
End If
If Not IsNull(cboPartName) Then
strSQL = strSQL & " and [PartName] = '" &
cboPartName & "' "
End If
If Not IsNull(cboPartNumber) Then
strSQL = strSQL & " and [PartNumber] = '" &
cboPartNumber & "' "
End If
If Not IsNull(cboRefNumber) Then
strSQL = strSQL & " and [RefNumber] = '" &
cboRefNumber & "' "
End If

'set the subform to strSQL
subfrmProductbyMultipleItems.Form.RecordSource = strSQL
& " ORDER BY [Make] ASC, [Model] ASC, [Year] ASC,
[PartName] ASC, [PartNumber] ASC, [RefNumber] ASC,
[Condition] ASC, [PartDescription1] ASC, [PartDescription2]
ASC, [New], [Quantity] ASC, [Comment1] ASC, [Comment2] ASC,
[Comment3] ASC, [Sold] ASC, [DateSold] ASC, [From] ASC,
[Location], [3] ASC, [4] ASC, [5] ASC, [6] ASC, [7] ASC,
[8] ASC, [9] ASC, [10] ASC"
subfrmProductbyMultipleItems.Requery
Click to expand...


.
Click to expand...
 
J

jfp

looking at your original post -- you have this:
strSQL = "Select [ID], [Make], [Model], [Year], [PartName],
[PartNumber], [RefNumber], [Condition], [PartDescription1],
[PartDescription2], [New], [Quantity], [Comment1],
[Comment2], [Comment3], [Sold], [DateSold], [From],
[Location], [3], [4], [5], [6], [7], [8], [9], [10] from
tblProduct where "
If Not IsNull(cboMake) Then
strSQL = strSQL & "[Make] = '" & cboMake & "'"
End If
If Not IsNull(cboModel) Then
strSQL = strSQL & " and [Model] = '" & cboModel & "' "
End If
Click to expand...
Click to expand...

Or, more simply,
strSQL = "Select <fields> from tblProduct where "
If Not IsNull(cboMake) Then
strSQL = strSQL & "[Make] = '" & cboMake & "'"
End If
If Not IsNull(cboModel) Then
strSQL = strSQL & " and [Model] = '" & cboModel & "' "
End If
Click to expand...
Click to expand...
Now, if cboMake IS Null but cboModel IS NOT Null, you will get something
like this:
strSQL = "Select <fields> from tblProduct where and [Model] = '" & cboModel & "' "
Click to expand...
Click to expand...

This will give you a syntax error.
I have no idea if that is your problem, but it is A problem.
-=-=-=-=
Perhaps I should have only posted the SQL select statement?

strSQL = "Select [ID], [Make], [Model], [Year], [PartName],
[PartNumber], [RefNumber], [Condition], [PartDescription1],
[PartDescription2], [New], [Quantity], [Comment1],
[Comment2], [Comment3], [Sold], [DateSold], [From],
[Location], [3], [4], [5], [6], [7], [8], [9], [10] from
tblProduct where "
Click to expand...

if statements

'set the subform to strSQL
subfrmProductbyMultipleItems.Form.RecordSource = strSQL
Click to expand...
-----Original Message-----
I have a form/subform that has 6 combo boxes and based on
the selection, the subform is updated accordingly. This was
working until I needed to add the field "ID".

Now with that field in the select statement, it only
returns the first record in the table. If I take out the
field "ID" from the select statement, I get "#Name?". Why
would it do this and what do I need to do to fix it?

Thank you in advance.

Ginger

Here is my statement:

strSQL = "Select [ID], [Make], [Model], [Year], [PartName],
[PartNumber], [RefNumber], [Condition], [PartDescription1],
[PartDescription2], [New], [Quantity], [Comment1],
[Comment2], [Comment3], [Sold], [DateSold], [From],
[Location], [3], [4], [5], [6], [7], [8], [9], [10] from
tblProduct where "
If Not IsNull(cboMake) Then
strSQL = strSQL & "[Make] = '" & cboMake & "'"
End If
If Not IsNull(cboModel) Then
strSQL = strSQL & " and [Model] = '" & cboModel & "' "
End If
If Not IsNull(cboYear) Then
strSQL = strSQL & " and [Year] = " & cboYear
End If
If Not IsNull(cboPartName) Then
strSQL = strSQL & " and [PartName] = '" &
cboPartName & "' "
End If
If Not IsNull(cboPartNumber) Then
strSQL = strSQL & " and [PartNumber] = '" &
cboPartNumber & "' "
End If
If Not IsNull(cboRefNumber) Then
strSQL = strSQL & " and [RefNumber] = '" &
cboRefNumber & "' "
End If

'set the subform to strSQL
subfrmProductbyMultipleItems.Form.RecordSource = strSQL
& " ORDER BY [Make] ASC, [Model] ASC, [Year] ASC,
[PartName] ASC, [PartNumber] ASC, [RefNumber] ASC,
[Condition] ASC, [PartDescription1] ASC, [PartDescription2]
ASC, [New], [Quantity] ASC, [Comment1] ASC, [Comment2] ASC,
[Comment3] ASC, [Sold] ASC, [DateSold] ASC, [From] ASC,
[Location], [3] ASC, [4] ASC, [5] ASC, [6] ASC, [7] ASC,
[8] ASC, [9] ASC, [10] ASC"
subfrmProductbyMultipleItems.Requery

.
Click to expand...
Click to expand...
 
Top