Controls on a Continuos Forms Subform

  • Thread starter Thread starter Robin Hickman
  • Start date Start date
R

Robin Hickman

Ok here's the situation:

I have a main form on which I enter and display client contact
information that comes from the table "Clients". Linked to the table
"Clients" by a one to many relationship is a table called
"Client_Accounts". This table stores transaction records for my clients.
On the main form is a Continuous Forms view subform on which I enter and
display the records from Client_Accounts. In other words, when I select
a client on the main form, the subform shows all the transactions
pertaining to that client. Simple enough so far even for me to figure out.

The data stored in Client_Accounts is very summary in nature. Besides
the "ID" type fields, each record contains the date, description, total
charges and amount paid for each transaction. The last two fields are
used in two calculated fields at the bottom of the subform that tell me
at a glance how much a client has spent and what his or her balance is.
This works very well for the purposes for which I designed it. Now,
however, I want to add more functionality to the database and I have
reached the limits of my knowledge (passed them actually, I only got
this far with much help!)

I want to add another table, probably named "Transaction_Details",
linked to Client_Accounts so that there are many Transaction_Detail
records for each Client_Account record. Essentially, Transaction_Details
will contain individual invoice line items, linked to the invoice date
and totals in Client_Accounts. Simple enough, I can handle the table and
relationship.

Where I am stymied is in how to display this in the forms. I don't want
the Transaction_Details records to be visible all the time, just when I
want to see them or edit them. I picture a form that I can pop up with
a button click. That is what I don't know how to implement, mainly
because the Client_Accounts records are displayed as Continous Forms. If
I put a button at the bottom of that subform, how does it know which
record I want to see the details for? Or is there a way to display a
button for each record displayed on the subform, maybe at the end of
each row? That seems like it would require a button to be created and
code attached every time a record is added. Probably not practical.
Maybe this requires a different approach altogether.

Thanks for any help on this problem. If I've not been clear about what
I'm trying to do, please let me know and I'll try again.

Thanks,
Robin
 
Use the double-click event of one or more controls on the subform. I assume
that you have the ID of the subform record, probably hidden, in each row so
you can use code similar to (air code):

Sub MyControl_DoubleClick()
DoCmd.OpenForm "frmMyPopUp",,,,"ID =" & Me.txtID
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Thanks,

I think I understand, but I'm not making it work right. I get a Type
Mismatch error. I don't think I understand that last argument. What
exactly is it supposed to be? (the sub form I am calling the the popup
form from is Client_Account and the ID of the record is
Client_Account_ID, which is linked to a field of the same name in the
table that the popup form is based on. That table and popup form are
both named Transaction_Detail.)
 
The type mismatch is probably coming from a datatype in txtID which is text
instead of numeric: Try using something like:

DoCmd.OpenForm "Transaction_Detail",,,,"Client_Account_ID = '" &
Me.Client_Account_ID & "'"
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
I double checked the tables, Client_Account_ID is type autonumber in the
table Client_Account and number in the table Transaction_Detail.

this is my exact code that is generating the error (It's the
Transaction_Detail form I am wanting to open, from the Client_Account form

DoCmd.OpenForm "Transaction_Detail",,,, "Client_Account_ID =" &
Me.Client_Account_ID
 
Back
Top