Query based on input?

  • Thread starter Thread starter Steven W
  • Start date Start date
S

Steven W

I have a database containing all information on a number
of projects which we are monitoring. We have a form based
on this project database which displays all the project
information. I want to be able to enter the project number
in the form (or do I need to produce a replicate form
based on the original form) and all related information to
that specific project is displayed either in a form or a
report (whichever is easiest). I am not sure whether this
is a query or a filter or what?

Grateful any advice from an Access newbie.
 
Use the "standard" record jumping technique with an unbound cbo in
the Header of your form. The main part of the form is the details for a
project.

In this example, the combobox (cbo) is based on a query of LastName,
FirstName, ID and the bound column is 3 for the ID which is unique. The user
sees a list in LastName order and for people with the same last name can
scroll down just a bit and pick the exact person. Then in the AfterUpdate
event of the cbo (code that runs after something is picked) you use this
type of code: (My cbo is named cboAgent2 and AgentID is an Integer)

Private Sub cboAgent2_AfterUpdate()
On Error GoTo Err_cboAgent2_AfterUpdate

Dim rs As Recordset
Set rs = Me.RecordsetClone
Criteria = "[AgentID] = " & Me![cboAgent2]
rs.FindFirst Criteria
If rs.NoMatch Then
MsgBox ("There is no AgentID = " & Me![cboAgent2])
Me![cboAgent2] = Me![AgentID]
Else
Me.Bookmark = rs.Bookmark
Me![cboAgent2].SetFocus
End If

Exit_cboAgent2_AfterUpdate:
Exit Sub

Err_cboAgent2_AfterUpdate:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source &
Chr(13) & Err.Description)
Resume Exit_cboAgent2_AfterUpdate

End Sub
 
Thanks for your help.
Could you elaborate a bit more on each step you have
described, particularly the cbo as I can't get it to
function at all.
I have managed to obtain the result I wanted but as a
parameter query (not using your advice though)- I would
prefer to be able to use a pull down menu in the Project
No. field as the only search parameter
tkks
-----Original Message-----
Use the "standard" record jumping technique with an unbound cbo in
the Header of your form. The main part of the form is the details for a
project.

In this example, the combobox (cbo) is based on a query of LastName,
FirstName, ID and the bound column is 3 for the ID which is unique. The user
sees a list in LastName order and for people with the same last name can
scroll down just a bit and pick the exact person. Then in the AfterUpdate
event of the cbo (code that runs after something is picked) you use this
type of code: (My cbo is named cboAgent2 and AgentID is an Integer)

Private Sub cboAgent2_AfterUpdate()
On Error GoTo Err_cboAgent2_AfterUpdate

Dim rs As Recordset
Set rs = Me.RecordsetClone
Criteria = "[AgentID] = " & Me![cboAgent2]
rs.FindFirst Criteria
If rs.NoMatch Then
MsgBox ("There is no AgentID = " & Me![cboAgent2])
Me![cboAgent2] = Me![AgentID]
Else
Me.Bookmark = rs.Bookmark
Me![cboAgent2].SetFocus
End If

Exit_cboAgent2_AfterUpdate:
Exit Sub

Err_cboAgent2_AfterUpdate:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source &
Chr(13) & Err.Description)
Resume Exit_cboAgent2_AfterUpdate

End Sub

--
Joe Fallon
Access MVP



Steven W said:
I have a database containing all information on a number
of projects which we are monitoring. We have a form based
on this project database which displays all the project
information. I want to be able to enter the project number
in the form (or do I need to produce a replicate form
based on the original form) and all related information to
that specific project is displayed either in a form or a
report (whichever is easiest). I am not sure whether this
is a query or a filter or what?

Grateful any advice from an Access newbie.
Click to expand...


.
Click to expand...
 
Back
Top