Selecting a record in a list box

  • Thread starter Thread starter Bob Wickham
  • Start date Start date
B

Bob Wickham

Hi,
I have a form based on two queries. I used the form wizard to create it
and the wizard has done what I hoped for and split the form into a
Form/Sub-Form arrangement.
By cycling through the records in the main form using the record
selector buttons the records in the sub-form change as expected and I am
able to add new records in the sub-form.

Instead of cycling through I would like to be able to select a record in
the main form by choosing a record in a list box, or even better, by
entering characters and having Access auto-complete until the desired
record is found.

Once the record is found, the associated records in the sub-form would
obviously change.

This seems like a really straight forward thing to do but many google
searches and downloaded tutorials later and I'm still stuck.

I purposefully haven't included details of my tables and relationships
here because it seems like a generic type of problem but I am happy to
do so.
Any ideas.

thanks
Bob Wickham
 
Bob Wickham said:
Hi,
I have a form based on two queries. I used the form wizard to create
it and the wizard has done what I hoped for and split the form into a
Form/Sub-Form arrangement.
By cycling through the records in the main form using the record
selector buttons the records in the sub-form change as expected and I
am able to add new records in the sub-form.

Instead of cycling through I would like to be able to select a record
in the main form by choosing a record in a list box, or even better,
by entering characters and having Access auto-complete until the
desired record is found.

Once the record is found, the associated records in the sub-form would
obviously change.

This seems like a really straight forward thing to do but many google
searches and downloaded tutorials later and I'm still stuck.

I purposefully haven't included details of my tables and relationships
here because it seems like a generic type of problem but I am happy to
do so.
Any ideas.
Click to expand...

More than anything else this sounds like the common -- and
wizard-supported -- technique of using a combo box to locate a record on
your main form. If you have wizards turned on, add a combo box to your
form and tell the combo box wizard that you want this combo box to "find
a record on my form". Answer a few questions for the wizard, and bob's
your uncle. Well, no, apparently Bob is you; but you get the idea.
 
More than anything else this sounds like the common -- and
wizard-supported -- technique of using a combo box to locate a record on
your main form. If you have wizards turned on, add a combo box to your
form and tell the combo box wizard that you want this combo box to "find
a record on my form". Answer a few questions for the wizard, and bob's
your uncle. Well, no, apparently Bob is you; but you get the idea.
Click to expand...
Hi Dirk,
I did as you suggested and abracadabra, I have a new uncle. but,
now I can't add records in the sub-form.

I started with a main form created from a query

SELECT tblLoan.LoanNo, tblLoanType.LoanType, tblLender.Lender,
tblClient.Client
FROM tblLoanType INNER JOIN (tblLender INNER JOIN (tblClient INNER JOIN
tblLoan ON tblClient.ClientID = tblLoan.Client) ON tblLender.LenderID =
tblLoan.Lender) ON tblLoanType.LoanTypeID = tblLoan.LoanType;


displaying 4 text boxes.
The first text box, LoanNo, is the basis for the other 3 text boxes,
LoanType, Lender, Client.
To allow the user to see associated records all the time rather than
just when clicking on the combo box, I left the 4 text boxes as they
were and added the combo box.
In the AfterUpdate event of the combo box I added this code

Private Sub ComboLoanNo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LoanNo] = '" & Me![ComboLoanNo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Now by choosing a LoanNo in the combo box the contents of the original 4
text boxes changes.

I have a sub-form based on a query

SELECT tblLoan.LoanNo, tblCommission.PaymentDate, tblCommission.Payment,
tblCommission.GST, tblCommission.DateBanked, tblCommission.LoanBalance
FROM tblLoan INNER JOIN tblCommission ON
tblLoan.LoanNo=tblCommission.LoanNo;

and again I have used the LoanNo as the controlling factor.
With this code in the LoanNo text box of the Sub-form
=Forms!frmLoanClientParent!ComboLoanNo
I am able to display all commission payments received in respect of the
LoanNo chosen in the Combo box.

I want to be able to add new records in this sub-form.
It lets me click New Record and it presents me with blank text boxes to
type in but it won't accept any entries. (just annoying beeps).

Bob
 
Bob Wickham said:
Hi Dirk,
I did as you suggested and abracadabra, I have a new uncle.
Click to expand...

but, now I can't add records in the sub-form.
Click to expand...

Are you saying this started only after adding the combo box, and you did
nothing else? That seems exceedingly strange, and I'm inclined to doubt
that it can be the case.
I started with a main form created from a query

SELECT tblLoan.LoanNo, tblLoanType.LoanType, tblLender.Lender,
tblClient.Client
FROM tblLoanType INNER JOIN (tblLender INNER JOIN (tblClient INNER
JOIN tblLoan ON tblClient.ClientID = tblLoan.Client) ON
tblLender.LenderID = tblLoan.Lender) ON tblLoanType.LoanTypeID =
tblLoan.LoanType;


displaying 4 text boxes.
The first text box, LoanNo, is the basis for the other 3 text boxes,
LoanType, Lender, Client.
To allow the user to see associated records all the time rather than
just when clicking on the combo box, I left the 4 text boxes as they
were and added the combo box.
In the AfterUpdate event of the combo box I added this code

Private Sub ComboLoanNo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LoanNo] = '" & Me![ComboLoanNo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Click to expand...

Note: It would be a good idea to change this condition:

If Not rs.EOF

to this:

If Not rs.NoMatch

The wizard gets it wrong, though it still works so long as the LoanNo in
the combo box is always found in the recordset.
Now by choosing a LoanNo in the combo box the contents of the
original 4 text boxes changes.

I have a sub-form based on a query

SELECT tblLoan.LoanNo, tblCommission.PaymentDate,
tblCommission.Payment, tblCommission.GST, tblCommission.DateBanked,
tblCommission.LoanBalance FROM tblLoan INNER JOIN tblCommission ON
tblLoan.LoanNo=tblCommission.LoanNo;

and again I have used the LoanNo as the controlling factor.
With this code in the LoanNo text box of the Sub-form
=Forms!frmLoanClientParent!ComboLoanNo
I am able to display all commission payments received in respect of
the LoanNo chosen in the Combo box.

I want to be able to add new records in this sub-form.
It lets me click New Record and it presents me with blank text boxes
to type in but it won't accept any entries. (just annoying beeps).
Click to expand...

You've set up your subform incorrectly. You don't need the join to
tblLoan, and the LoanNo text box on the subform should not have that
=Forms!... expression as its controlsource. You do need to set the
subform control's Link Master Fields and Link Child Fields properties,
which you don't mention.

Set your subform's RecordSource property to this:

SELECT
tblCommission.LoanNo, tblCommission.PaymentDate,
tblCommission.Payment, tblCommission.GST,
tblCommission.DateBanked,
tblCommission.LoanBalance
FROM tblCommission;

Note: the above formatting is just for easy reading. In the
RecordSource property, it should all be on one line. Add an ORDER BY
clause if you want the records to be sorted. Remove the

Now, make sure that your subform has a text box on it that is bound to
LoanNo. There's now no tblLoan.LoanNo in the subform's recordsource, so
if you had a text box with "tblLoan.LoanNo" in its ControlSource
property, change that to just "LoanNo". I think you said you were using
the expression "=Forms!frmLoanClientParent!ComboLoanNo" in there; if
so, change that to "LoanNo".

You probably don't need to actually display the LoanNo on the subform,
so I'd recommend making the LoanNo text box invisible, and size it very
small so it's not in the way even in design view. I usually give these
text boxes a width of zero.

Finally, make sure the Link Master Fields and Link Child Fields of the
subform control (the control on the main form that displays the subform)
are both set to "LoanNo".

That ought to do it.
 
Dirk said:
Hi Dirk,
I did as you suggested and abracadabra, I have a new uncle.
Click to expand...


but, now I can't add records in the sub-form.
Click to expand...


Are you saying this started only after adding the combo box, and you did
nothing else? That seems exceedingly strange, and I'm inclined to doubt
that it can be the case.

I started with a main form created from a query

SELECT tblLoan.LoanNo, tblLoanType.LoanType, tblLender.Lender,
tblClient.Client
FROM tblLoanType INNER JOIN (tblLender INNER JOIN (tblClient INNER
JOIN tblLoan ON tblClient.ClientID = tblLoan.Client) ON
tblLender.LenderID = tblLoan.Lender) ON tblLoanType.LoanTypeID =
tblLoan.LoanType;


displaying 4 text boxes.
The first text box, LoanNo, is the basis for the other 3 text boxes,
LoanType, Lender, Client.
To allow the user to see associated records all the time rather than
just when clicking on the combo box, I left the 4 text boxes as they
were and added the combo box.
In the AfterUpdate event of the combo box I added this code

Private Sub ComboLoanNo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LoanNo] = '" & Me![ComboLoanNo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Click to expand...


Note: It would be a good idea to change this condition:

If Not rs.EOF

to this:

If Not rs.NoMatch

The wizard gets it wrong, though it still works so long as the LoanNo in
the combo box is always found in the recordset.

Now by choosing a LoanNo in the combo box the contents of the
original 4 text boxes changes.

I have a sub-form based on a query

SELECT tblLoan.LoanNo, tblCommission.PaymentDate,
tblCommission.Payment, tblCommission.GST, tblCommission.DateBanked,
tblCommission.LoanBalance FROM tblLoan INNER JOIN tblCommission ON
tblLoan.LoanNo=tblCommission.LoanNo;

and again I have used the LoanNo as the controlling factor.
With this code in the LoanNo text box of the Sub-form
=Forms!frmLoanClientParent!ComboLoanNo
I am able to display all commission payments received in respect of
the LoanNo chosen in the Combo box.

I want to be able to add new records in this sub-form.
It lets me click New Record and it presents me with blank text boxes
to type in but it won't accept any entries. (just annoying beeps).
Click to expand...


You've set up your subform incorrectly. You don't need the join to
tblLoan, and the LoanNo text box on the subform should not have that
=Forms!... expression as its controlsource. You do need to set the
subform control's Link Master Fields and Link Child Fields properties,
which you don't mention.

Set your subform's RecordSource property to this:

SELECT
tblCommission.LoanNo, tblCommission.PaymentDate,
tblCommission.Payment, tblCommission.GST,
tblCommission.DateBanked,
tblCommission.LoanBalance
FROM tblCommission;

Note: the above formatting is just for easy reading. In the
RecordSource property, it should all be on one line. Add an ORDER BY
clause if you want the records to be sorted. Remove the

Now, make sure that your subform has a text box on it that is bound to
LoanNo. There's now no tblLoan.LoanNo in the subform's recordsource, so
if you had a text box with "tblLoan.LoanNo" in its ControlSource
property, change that to just "LoanNo". I think you said you were using
the expression "=Forms!frmLoanClientParent!ComboLoanNo" in there; if
so, change that to "LoanNo".

You probably don't need to actually display the LoanNo on the subform,
so I'd recommend making the LoanNo text box invisible, and size it very
small so it's not in the way even in design view. I usually give these
text boxes a width of zero.

Finally, make sure the Link Master Fields and Link Child Fields of the
subform control (the control on the main form that displays the subform)
are both set to "LoanNo".

That ought to do it.
Click to expand...
Hi Dirk,
Yes, that did it. Thankyou.
I did say in my first post that I had it working by using the wizard but
before I added the combo box. I would have bet my mother-in-law that
that was the case but I can't seem to repeat it and have since altered
the original so I guess I must have been dreaming.

I'm getting there with this Access stuff. I'm at the stage where what
ought to take 5 minutes can take 5 days and I'm always grateful for the
newsgroup community.

Bob Wickham
 
Back
Top