Question about Subforms, VBA, Update, and Parameters

  • Thread starter Thread starter AmandaH
  • Start date Start date
A

AmandaH

Good morning all,

I am trying to create a simple search form. What I would like to do is
have a list of possible search fields, like project number or employer
name, that would be set up as text fields on an Access 2003 form. I
want to take the value entered into one of these fields and use it as
a value in a query. The trick here is that I want everything on ONE
form.

What would happen is this
1) The user would enter a project number
2) Then hit the search button
3) The subform , on the same page, would then update so that it only
contained the records with the value entered in the project number
field.

Does anyone have any ideas of how this could be done?

Thanks,

~Amanda~
 
Amanda,

Once a value had been entered, you would need to set the Record source
of the whole form, so if someone entered a project number you would
need to change the record source to "Projects".

Then the subform can link with the main form using the master/child
field options. That way when number 2206 is entered, for example, only
records in the related table (maybe employees) would show up.

Tell me if you need that explained in a bit more detail.

Greg
 
Thanks for the replay and advice. I am not too sure how to work with
"master/child field options". However I was able to refresh my subform
using the "DoCmd.Requery" Method in VBA along with setting the
criteria in me query, in which my Subform was base, to [Forms]!
[OpendingViewForOfficers]![ProjectNumberSearch].

Where "OpendingViewForOfficers" was my main form and
"ProjectNumberSearch" was the name given to the textField located on
my main form.

I used the "DoCmd.Requery" method when the user hit the search button.

Just incase any other developer was having the same problem I was.

Thanks again,
~Amanda~
 
The problem that I seem to be having now is that I can't do more the
two OR staments or else the records keep adding to one another.



what i have now:

SELECT HireData.ProjectNo, HireData.EmployerName, HireData.FirstName,
HireData.LastName, HireData.Sector, HireData.Location,
HireData.StartDate, HireData.EndDate, HireData.RegionalRiding,
HireData.BirthDate, HireData.Office
FROM HireData
WHERE (((HireData.ProjectNo)=[Forms]![OpendingViewForOfficers]!
[ProjectNumberSearch])) OR (((HireData.EmployerName) Like "*" &
[Forms]![OpendingViewForOfficers]![EmployerNameSearch] & "*")) OR
(((HireData.FirstName) Like "*" & [Forms]![OpendingViewForOfficers]!
[FirstNameSearch] & "*")) OR (((HireData.LastName) Like "*" & [Forms]!
[OpendingViewForOfficers]![LastNameSearch] & "*"));
 
Back
Top