Limiting size of Access recordset
Thanks, I actually worked it out by myself - eventually!
The code for the form at the current stage of development is as follows:
Private Sub Form_Load()
Dim fSQL As String
fSQL = "SELECT * FROM Projects"
Adodc1.RecordSource = fSQL
Adodc1.Refresh
End Sub
Private Sub cboProduct_Click(Area As Integer)
Dim strSQL As String
strSQL = "Select projects.proj_num "
strSQL = strSQL & "from products inner join projects "
strSQL = strSQL & "on products.product_id=projects.product_id "
strSQL = strSQL & "where products.product = " & Chr(39)
strSQL = strSQL & Me!cboProduct
strSQL = strSQL & Chr(39)
Adodc1.RecordSource = strSQL
Adodc1.Refresh
If Adodc1.Recordset.RecordCount <> 0 Then
Me!cboPassport = Adodc1.Recordset.Fields(0)
Else
MsgBox "There are no Projects associated with this Product! ",
vbOKOnly
Exit Sub
End If
End Sub
Private Sub cboPassport_Click(Area As Integer)
On Error Resume Next
Dim pSQL As String
pSQL = "SELECT Projects.proj_num, "
pSQL = pSQL & "Projects.proj_description, " ' Text 0
pSQL = pSQL & "Projects.target_date, " ' Text 1
pSQL = pSQL & "ProjType.proj_type, " ' Text 2
pSQL = pSQL & "Clients.client_name, " ' Text 3
pSQL = pSQL & "writer.lastname, " ' Text 4
pSQL = pSQL & "authors.lastname, " ' Text 5
pSQL = pSQL & "Managers.lastname," ' Text 6
pSQL = pSQL & "Projects.MinSales, " ' Text 7
pSQL = pSQL & "Projects.MaxSales " ' Text 8
pSQL = pSQL & "FROM Managers, ProjType, authors, writer, "
pSQL = pSQL & "projects INNER JOIN (Products INNER JOIN clients "
pSQL = pSQL & "on products.client_id=clients.client_id) "
pSQL = pSQL & "ON products.product_id=projects.product_id "
pSQL = pSQL & "where products.product= " & Chr(39)
pSQL = pSQL & Me!cboProduct
pSQL = pSQL & Chr(39) & " AND projects.proj_num="
pSQL = pSQL & Me!cboPassport
pSQL = pSQL & " AND (projects.writer_id=writer.writer_id) "
pSQL = pSQL & "AND (projects.author_id=authors.author_id) "
pSQL = pSQL & "AND (ProjType.proj_type_id=Projects.proj_type_id) "
pSQL = pSQL & "AND (Managers.manager_id=Projects.manager_id)"
Adodc1.RecordSource = pSQL
Adodc1.Refresh
Text1(0).Text = Adodc1.Recordset.Fields(1)
Text1(1).Text = Adodc1.Recordset.Fields(2)
.
.
.
End Sub
Now to move on to being able to use combo boxes where there could be
multiple choices, and then to be able to update the original record - ain't
learning fun!
Rod
Quote:
> Hi Rod. You don't give any examples of your SQL or code, so we're
thrashing
> about in the dark. But the 'parameters required' message suggests, among
> other things, that your SQL is not being interpreted correctly. Try
placing
> brackets around key phrases. Or post it here, so we can look at it.
> >As a learning exercise with practical value, I want to build a VB6
> front-end
> >to an Access 2000 database, for several (10-15) users to access via the
> >network. I have no problems connecting to the database via ODBC, but I
> would
> >like to limit the number of records displayed using a couple of combo
boxes
> >(clients and project_number).
> >I can get the clients combo box to work using ADO, but am struggling to
> find
> >out how to get the second one to work - I have a SQL string that
> >incorporates the return from 'clients', but this gives me parameter
> required
> >error messages.
> >Any ideas much appreciated.
> >Rod
