Query by form - VBA

[Craig]

I have one donor table and one donations table. I have two separate forms
to input the different types of donors (Company/Individual)

Each Donor form has a donations SubForm.

I want to do a query by form using the donation ID when clicking on a
command button.

Originally I had the following in the query and a command button to open the
query:

[Forms]![frmCompanies - LPS]![frmDonations]![DonationID]

Of course this would only work for the company form.

Without making another query (which would be the easy way round) is there
some way where I can change [frmCompanies - LPS] to a variable that selects
the current form.

I did try following VBA code but without any success.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("qryDonationsLetter")
qdf.Parameters(0) = Forms![frmCompanies - LPS]!frmDonations!DonationID
Set rst = qdf.OpenRecordset ' Open recordset on the query
If rst.RecordCount > 0 Then
DoCmd.OpenQuery "qryDonationsLetter"
End If

rst.Close
qdf.Close

End Sub

Thanks
Craig

 

[Barry Gilbert]

Personally, I would never develop something that uses a query as a user
interface. Consider using comboboxes for your criteria. These can be
populated from the values in each field of the table.

Also, why do you have two forms bound to the same table? Consider
adding a DonationType column to the table which will indicate whether
it's a company or individual and then select the type on a single form
by using an option group or combobox. This might eliminate your first
problem.

 

[Craig]

To explain further:

I have 1 Table that records, Individuals, Companies, County Departments,
Government Departments and include field which records the donor type.

From individuals I recieve Pledges of money(tblPledge), from all the others
I receive actual Money(tblDonation).

Individuals, Companies, County & Govt are all entered differently but end up
in the same table. I didnt create a single form as there would be too much
redundant information on the form. So I created separate forms.

However, the donations table records income from three groups and on a rare
occasion from an Individual. I created a linked subform to display the
donations for each donor.

Now by clicking on a command button in the subform I want to run query.

I can create 4 queries and use
[Forms]![frmName]![subfrmDonations]![DonationID], but I am trying to work
out if I can just have one query that uses a Form Name variable.

Thanks
Craig