Query based on input?

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.
 
J

Joe Fallon

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
 
S

Steven W

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.


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top