subform returns only 1 record

  • Thread starter Thread starter Ginger
  • Start date Start date
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
 
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...
 
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.)
 
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...
 
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...
 
Back
Top