setting control focus

[jer]

I am creating a data base for customer payments. Some
customers have multiple accounts so I have created a
Customer table and a sub-account table from which I have
created forms. I have created a 1 to many relationship
between the customer table and the sub account table.
I have created a payments table, from which I have also
created a form, that includes customer_Id and sub
account_Id.
The fields in the customer form are
CustomerId, Name, Address ...
The fields in the SubAccount form are
SubAccountId, CustomerId, SubAccountNo
The fields in the Payments form are
PaymentId, CustomerId, SubAccountId, PaymentAmount,
DatePaid ...
Is it possible in the payments form that, if there is no
sub account associated with the customer making a payment
that the sub account_Id control is skipped and the focus
is set to the next control, payment amount
Thanks in advance for any help/suggestions
jer

 

[Ken Snell]

Yes, likely this is doable. But I'm not understanding in my mind's eye how
your forms are being used. Is one or more of the forms being used as a
subform in another form? Are the forms all being used on their own?

More info about the setup, please.

 

[jer]

Ken, the Sub Account form is a sub form of the customer
form. The payments form is being used on its own. Also,
I am not sure if this would be of any bearing to this
question but these are not the only tables/forms in the
database as eventually I would like to create a report of
all payments by customer (including sub accounts) showing
their status, that is, whether paid or still outstanding.
For now though, ideally I do not want the user to check
each time they input the customer Id whether this
customer has a sub account or not.
hope this clarifies the set up for you.
Thanks for your response and hope to hear from you again
jer

 

[Ken Snell]

So what you want is the following:

When you're working in the form that is bound to the Payments table, as you
make a record current, if there is no record in the Sub Account table for
this customer, you want the focus to start on the payment control?

If this is correct, you could use code similar to this in the OnCurrent
event of the Payments form:


Private Sub Form_Current()
If DCount("*", "Sub Account", "[CustomerID] = " & Me.CustomerID) = 0 _
Then Me.Payments.SetFocus
End If


This code looks for how many records in the Sub Account table have the
CustomerID value that is being used on the Payments form. If the answer is
zero (no records), then the focus is put on the Payments control.