Populating fields based on combo box or subform

Question

My frmAccount has a subform, subAcctAppointment, and they have a relationship through ClientID field.  Ideally I'd like to be able to click the checkbox in the subform for one record in the datasheet, and have an event populate the fields in frmAccount, so I can save the appointment info to tblAccount. I tried the following based on what I used previously to populate from a combo box that works well. I put the following in an event for the checkbox "on click"  It didn't work.

Me.txtAcctClientID.Value =Me.subAcctAppointment. ClientID

Me.txtAcctApptID.Value =Me.subAcctAppointment. ApptID

Me.txtAcctApptDate.Value = Me.subAcctAppointment.ApptDate

Me.txtAcctAmount.Value =Me.subAcctAppointment.Revenue

Then I tried to use a combo box, however it shows all appointments and not just those for the client I pulled up in the main form (frmAccount). I can't find a way to filter what appears in the combo box to show only for the ClientID showing in the main form.  I don't like this method as well, but if the first one can't happen for some reason, I will take an alternative. But if this one won't I may be sunk! Any helpful ideas out there?

Answer 1

Hi Scott & Ken,

My original request was to better filter my combo box to include only those appointments belonging the client in the main form. I finally figured out to go to the row source for the combo box and click the [...] button. Then in the query screen I located the ClientID field and added the criteria below.

Field:  ClientID

Table:  tblAppointment

Criteria:  [Forms]![frmAccount]![ClientID]

This worked to restrict the combo box to only the one client's appointments. I found I still had a problem that it would not refresh the data when I clicked to get to the next record, and I had to hit 'refresh'. Then after experimenting I found that a macro to refresh worked when I put the refresh in an event on the form's properties (not on the field's) 'On Current'.

In response to your other points, I do see what you mean about the balance field not being in a table, and not all transactions would be tracked this way. If the client pays cash, then the revenue field in tblAppointment is the only record of the revenue/payment. Scott had a question too about payments; the client can make one payment for several appointments. This new functionality is so the client can run up a tab.

I am posting this in case you have any further caveats for me, and for others in the future who may have a similar challenge.  I still don't know how to populate 'txt' fields from a subform to save the data to a third table (not the table providing the main or subform data), but eventually, maybe I will figure out that too if I ever need it. 

Thanks guys!

Answer 2

The balances per account should not be saved in a column in a table.  This leaves the database wide open to update anomalies, and as Murphy's Law infallibly states, 'If something can go wrong, it will go wrong.'  The balances can be computed from the transaction amounts.

Computing balances is pretty standard.  Essentially the debit balance following each transaction is the sum of all debits less the sum of all credits per account up to and including the transaction.  The current balance is that following the latest transaction.  There are various ways of computing balances, of which the most efficient is by means of a JOIN of two instances of the transactions (appointments in your case) table.  Less efficient, is the use of a correlated subquery to return the balance.  Neither of these return an updatable result table, however, so if the query is to be used as the basis for an editable form it is necessary to resort to the VBA DSum function, which is the least efficient of all.

You'll find examples in Balances.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates how to compute balances in a variety of scenarios.  Note that the queries bring the TransactionID primary key into play to act as the tie breaker in the event of two or more transactions occurring on the same day.  This might not be necessary in your case if there can be one appointment only per account in any one day.

Answer 3

Forms don't hold data, tables hold data. So you don't save data to a form. So you need to rethink your process a bit.

Your tblAccount should look like this:

tbAccount

AccountID (PK Autonumber)

ClientID (FK)

ApptID (FK)

PaymentAmt

PaymentDate

I'm not sure what fees are or how they differ from Payments. You don't need to store the balance as that should be calculated. You don't need to store the total as that is in the Appointment table.

But I have to as one question before we go further. What happens if a Client has mutipe appointments before paying off. Is the client going to make one payment or multiple payments (for each appt). Are you going to apportion the payments or the client?

Answer 4

Hi Ken... it was very kind of you to take my question so earnestly, so allow me to rewind a bit. This app tracks client info and their appointments. The users  book the appointment on frmClient and subAppointment. This part works well. Now they have asked for the functionality to track amounts owed and payments. This way their clients can run a tab and pay every month or two if the client is trusted. The amount owed and the date incurred is kept the appointment table. I need to search for a client's appointments, pull up each one into the revenue (amount owed) field on the main form and then save the data to frmAccount so I can keep a running balance of what the client owns. I hope that makes sense.

For my main form, I copied from frmClient with its subform frmAppointments where the user books appointment with a particular client. It only shows that one client's appointments. After I copied frmClient and renaming it to frmAccount, I  added fields from tblAccount where I have AcctAmount, AcctFees, AcctTotal, AcctPayment, Acct Balance, AcctCLientID, AcctApptID, etc..

I need to have a way of looking up appointment info for one client at a time, and saving that data from the Appointment table to the Account table to track what is owed, when they pay, and how much they pay. Does that help?

Answer 5

Are the controls in the parent form to which you are attempting to assign the values bound controls?    The parent form is based on a table which models an Accounts entity type, while the subform's underlying table is modelling an Appointments entity type. So far so good.  However, if the controls are bound, you now appear to wish to assign attributes of a specific appointment to the referenced row in the Accounts table.

In relational database terms this would be fundamentally incorrect as the values are not attributes of the Accounts entity type.  If the controls are bound, what is the rationale behind what you are attempting?

Even harder to understand is why you would wish to assign the ClientID value to a control in the parent form.  As the form and subforms are linked on ClientID the key of the parent form's recordset is already the ClientID value, so what is the purpose of trying to assign it to the control?

If the controls in question are unbound, and are intended merely to show appointment data from whichever row is selected in the subform, then I can to some extent see why you might want to do this, in which case the code would be:

Me.Parent.txtAcctClientID = Me.ClientID

Me.Parent.txtAcctApptID = Me.ApptID

Me.Parent.txtAcctApptDate = Me.ApptDate  

Me.Parent.txtAcctAmount = Me.Revenue

though I can't see any useful reason for assigning either the ClientID or the ApptID values to unbound controls in the parent form.  Bear in mind also that if the controls are unbound they values in them would persist if you navigate to a different account in the parent form, unless you clear them with code in the parent form's Current event procedure.

I think it would help if you could explain what you are trying to achieve in real life business terms rather than how you are attempting to do so in terms of the user interface.