Trying to pass criteria from form to creat sql...help!!!

[BigFizil]

Below is my code to give me a PAGING type form...I currently have a form with text and combo boxes that pass parameters to a query and it works fine...the problem is the output only returns one item per page....I solved this by using my paging code for this form...but I want to set it up so a user can go to the combo/list boxes and it will limit the results accordingly....please help!!!


Option Compare Database
Option Explicit

Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset

Dim CurrentPage



Private Sub Form_Load()

CurrentPage = 1

Set cn = CurrentProject.Connection
Set rst = New ADODB.Recordset

With rst
.PageSize = 8
.ActiveConnection = cn
.Source = "select Development.[KinderNumber],Development.[family],Development.[Intro_Date],Development.[Client],Development.[MFG],Development.[Fixture],Development.[Dimensions (HxWxD)],Development.[MFG Price],Development.[Item Number], dp.[Image], dp.[Revision_ Letter] FROM developmentpictures dp RIGHT JOIN Development ON dp.[kindernum] = development.[kindernumber] WHERE dp.[Revision_ Letter] IN(select max([Revision_ Letter]) from developmentpictures where kindernum = dp.kindernum)ORDER BY Development.[Client], Development.[MFG],development.[intro_date],development.[Family]"
.LockType = adLockOptimistic
.CursorType = adOpenStatic
.Open









End With
'Set Me.Recordset = rst

UpdateControls
End Sub

Private Sub NextBtn_Click()
CurrentPage = CurrentPage + 1
UpdateControls
End Sub

Private Sub PrevBtn_Click()
CurrentPage = CurrentPage - 1
UpdateControls
End Sub

Private Sub UpdateControls()
Dim NumPages
Dim NumRecs

' Get the number of pages and records
NumPages = rst.PageCount
NumRecs = rst.RecordCount

PageLabel.Value = "Page " & CurrentPage & " of " & NumPages

If CurrentPage > NumPages Then CurrentPage = NumPages
If CurrentPage < 1 Then CurrentPage = 1

' Set the page we want to display
rst.AbsolutePage = CurrentPage

If CurrentPage = NumPages Then
DoCmd.GoToControl &quot;dbpixctl1&quot;
NextBtn.Enabled = False
Else
NextBtn.Enabled = True
End If

If (Not rst.EOF) Then
dbpixctl1.IMAGE = rst(&quot;Image&quot
Txtkn1.Value = rst(&quot;KinderNumber&quot
TxtDate1.Value = rst(&quot;Intro_Date&quot
Txtclient1.Value = rst(&quot;Client&quot
Txtmfg1.Value = rst(&quot;MFG&quot
Txtfix1.Value = rst(&quot;Fixture&quot
Txtdim1.Value = rst(&quot;Dimensions (HxWxD)&quot
Txtin1.Value = rst(&quot;Item Number&quot
txtmp1.Value = rst(&quot;MFG price&quot
Txtrev1.Value = rst(&quot;Revision_ Letter&quot
txtfam1.Value = rst(&quot;Family&quot
rst.MoveNext

Else
dbpixctl1.IMAGE = &quot;&quot;
Txtkn1.Value = &quot;&quot;
TxtDate1.Value = &quot;&quot;
Txtclient1.Value = &quot;&quot;
Txtmfg1.Value = &quot;&quot;
Txtfix1.Value = &quot;&quot;
Txtdim1.Value = &quot;&quot;
Txtin1.Value = &quot;&quot;
txtmp1.Value = &quot;&quot;
Txtrev1.Value = &quot;&quot;
txtfam1.Value = &quot;&quot;
End If

If (Not rst.EOF) Then
dbpixctl2.IMAGE = rst(&quot;Image&quot
Txtkn2.Value = rst(&quot;KinderNumber&quot
txtDate2.Value = rst(&quot;Intro_Date&quot
Txtclient2.Value = rst(&quot;Client&quot
Txtmfg2.Value = rst(&quot;MFG&quot
Txtfix2.Value = rst(&quot;Fixture&quot
Txtdim2.Value = rst(&quot;Dimensions (HxWxD)&quot
Txtin2.Value = rst(&quot;Item Number&quot
Txtmp2.Value = rst(&quot;MFG price&quot
Txtrev2.Value = rst(&quot;Revision_ Letter&quot
txtfam2.Value = rst(&quot;Family&quot
rst.MoveNext
Else
dbpixctl2.IMAGE = &quot;&quot;
Txtkn2.Value = &quot;&quot;
txtDate2.Value = &quot;&quot;
Txtclient2.Value = &quot;&quot;
Txtmfg2.Value = &quot;&quot;
Txtfix2.Value = &quot;&quot;
Txtdim2.Value = &quot;&quot;
Txtin2.Value = &quot;&quot;
Txtmp2.Value = &quot;&quot;
Txtrev2.Value = &quot;&quot;
txtfam2.Value = &quot;&quot;
End If

If (Not rst.EOF) Then
dbpixctl3.IMAGE = rst(&quot;Image&quot
Txtkn3.Value = rst(&quot;KinderNumber&quot
txtdate3.Value = rst(&quot;Intro_Date&quot
Txtclient3.Value = rst(&quot;Client&quot
Txtmfg3.Value = rst(&quot;MFG&quot
txtfix3.Value = rst(&quot;Fixture&quot
Txtdim3.Value = rst(&quot;Dimensions (HxWxD)&quot
Txtin3.Value = rst(&quot;Item Number&quot
Txtmp3.Value = rst(&quot;MFG price&quot
Txtrev3.Value = rst(&quot;Revision_ Letter&quot
txtfam3.Value = rst(&quot;Family&quot
rst.MoveNext
Else
dbpixctl3.IMAGE = &quot;&quot;
Txtkn3.Value = &quot;&quot;
txtdate3.Value = &quot;&quot;
Txtclient3.Value = &quot;&quot;
Txtmfg3.Value = &quot;&quot;
txtfix3.Value = &quot;&quot;
Txtdim3.Value = &quot;&quot;
Txtin3.Value = &quot;&quot;
Txtmp3.Value = &quot;&quot;
Txtrev3.Value = &quot;&quot;
txtfam3.Value = &quot;&quot;
End If


If (Not rst.EOF) Then
dbpixctl4.IMAGE = rst(&quot;Image&quot
Txtkn4.Value = rst(&quot;KinderNumber&quot
txtdate4.Value = rst(&quot;Intro_Date&quot
Txtclient4.Value = rst(&quot;Client&quot
Txtmfg4.Value = rst(&quot;MFG&quot
Txtfix4.Value = rst(&quot;Fixture&quot
Txtdim4.Value = rst(&quot;Dimensions (HxWxD)&quot
Txtin4.Value = rst(&quot;Item Number&quot
Txtmp4.Value = rst(&quot;MFG price&quot
Txtrev4.Value = rst(&quot;Revision_ Letter&quot
txtfam4.Value = rst(&quot;Family&quot
rst.MoveNext
Else

dbpixctl4.IMAGE = &quot;&quot;
Txtkn4.Value = &quot;&quot;
txtdate4.Value = &quot;&quot;
Txtclient4.Value = &quot;&quot;
Txtmfg4.Value = &quot;&quot;
Txtfix4.Value = &quot;&quot;
Txtdim4.Value = &quot;&quot;
Txtin4.Value = &quot;&quot;
Txtmp4.Value = &quot;&quot;
Txtrev4.Value = &quot;&quot;
txtfam4.Value = &quot;&quot;
End If

If (Not rst.EOF) Then
dbpixctl5.IMAGE = rst(&quot;Image&quot
Txtkn5.Value = rst(&quot;KinderNumber&quot
txtdate5.Value = rst(&quot;Intro_Date&quot
Txtclient5.Value = rst(&quot;Client&quot
Txtmfg5.Value = rst(&quot;MFG&quot
Txtfix5.Value = rst(&quot;Fixture&quot
Txtdim5.Value = rst(&quot;Dimensions (HxWxD)&quot
Txtin5.Value = rst(&quot;Item Number&quot
Txtmp5.Value = rst(&quot;MFG price&quot
Txtrev5.Value = rst(&quot;Revision_ Letter&quot
txtfam5.Value = rst(&quot;Family&quot
rst.MoveNext
Else
dbpixctl5.IMAGE = &quot;&quot;
Txtkn5.Value = &quot;&quot;
txtdate5.Value = &quot;&quot;
Txtclient5.Value = &quot;&quot;
Txtmfg5.Value = &quot;&quot;
Txtfix5.Value = &quot;&quot;
Txtdim5.Value = &quot;&quot;
Txtin5.Value = &quot;&quot;
Txtmp5.Value = &quot;&quot;
Txtrev5.Value = &quot;&quot;
txtfam5.Value = &quot;&quot;
End If


If (Not rst.EOF) Then
dbpixctl6.IMAGE = rst(&quot;Image&quot
Txtkn6.Value = rst(&quot;KinderNumber&quot
txtdate6.Value = rst(&quot;Intro_Date&quot
Txtclient6.Value = rst(&quot;Client&quot
Txtmfg6.Value = rst(&quot;MFG&quot
Txtfix6.Value = rst(&quot;Fixture&quot
Txtdim6.Value = rst(&quot;Dimensions (HxWxD)&quot
Txtin6.Value = rst(&quot;Item Number&quot
Txtmp6.Value = rst(&quot;MFG price&quot
Txtrev6.Value = rst(&quot;Revision_ Letter&quot
Txtfam6.Value = rst(&quot;Family&quot
rst.MoveNext

Else
dbpixctl6.IMAGE = &quot;&quot;
Txtkn6.Value = &quot;&quot;
txtdate6.Value = &quot;&quot;
Txtclient6.Value = &quot;&quot;
Txtmfg6.Value = &quot;&quot;
Txtfix6.Value = &quot;&quot;
Txtdim6.Value = &quot;&quot;
Txtin6.Value = &quot;&quot;
Txtmp6.Value = &quot;&quot;
Txtrev6.Value = &quot;&quot;
Txtfam6.Value = &quot;&quot;
End If


If (Not rst.EOF) Then
dbpixctl7.IMAGE = rst(&quot;Image&quot
Txtkn7.Value = rst(&quot;KinderNumber&quot
txtdate7.Value = rst(&quot;Intro_Date&quot
Txtclient7.Value = rst(&quot;Client&quot
Txtmfg7.Value = rst(&quot;MFG&quot
Txtfix7.Value = rst(&quot;Fixture&quot
Txtdim7.Value = rst(&quot;Dimensions (HxWxD)&quot
Txtin7.Value = rst(&quot;Item Number&quot
Txtmp7.Value = rst(&quot;MFG price&quot
Txtrev7.Value = rst(&quot;Revision_ Letter&quot
txtfam7.Value = rst(&quot;Family&quot
rst.MoveNext

Else
dbpixctl7.IMAGE = &quot;&quot;
Txtkn7.Value = &quot;&quot;
txtdate7.Value = &quot;&quot;
Txtclient7.Value = &quot;&quot;
Txtmfg7.Value = &quot;&quot;
Txtfix7.Value = &quot;&quot;
Txtdim7.Value = &quot;&quot;
Txtin7.Value = &quot;&quot;
Txtmp7.Value = &quot;&quot;
Txtrev7.Value = &quot;&quot;
txtfam7.Value = &quot;&quot;
End If

If (Not rst.EOF) Then
dbpixctl8.IMAGE = rst(&quot;Image&quot
Txtkn8.Value = rst(&quot;KinderNumber&quot
txtdate8.Value = rst(&quot;Intro_Date&quot
Txtclient8.Value = rst(&quot;Client&quot
Txtmfg8.Value = rst(&quot;MFG&quot
Txtfix8.Value = rst(&quot;Fixture&quot
Txtdim8.Value = rst(&quot;Dimensions (HxWxD)&quot
Txtin8.Value = rst(&quot;Item Number&quot
Txtmp8.Value = rst(&quot;MFG price&quot
Txtrev8.Value = rst(&quot;Revision_ Letter&quot
Txtfam8.Value = rst(&quot;Family&quot
rst.MoveNext

Else
dbpixctl8.IMAGE = &quot;&quot;
Txtkn8.Value = &quot;&quot;
txtdate8.Value = &quot;&quot;
Txtclient8.Value = &quot;&quot;
Txtmfg8.Value = &quot;&quot;
Txtfix8.Value = &quot;&quot;
Txtdim8.Value = &quot;&quot;
Txtin8.Value = &quot;&quot;
Txtmp8.Value = &quot;&quot;
Txtrev8.Value = &quot;&quot;
Txtfam8.Value = &quot;&quot;
End If


End Sub

 

[Ecniv]

1) build the Sql statement using a string (then you can manipulate the where clause. Then you can refresh/requery/reopen the recordset as applicable.

2) use less code - the whole of the bottom section (sub updatecontrols) is the same except for the number on the control. If you are on the forms code, use me.Controls(&quot;txtfam&quot; & number) where number is in a loop 1 to 8. Should also make it all easier to read/debug.


Vince

 

[BigFizil]

??? I tried to copy the SQL that ACCESS created for my query but its giving me nothing but problems...
here is the format I am using in my query...


[Forms]![Product Development Search Form]![cboprofam] Or NZ([Forms]![Product Development Search Form]![cboprofam],&quot;&quot = &quot;&quot;

please help!