johnberniejones
asked on
Copy Value from ListBox to a bound Text box on subform
I have a problem with a query I am using for a subform.
The main form has an unbound listbox (lstConsultDate) that displays tblConsult, this is bound to another lstbox from tblClient. When I select the Client, it displays the consultations for that client that do not have a payment attached. I am using this to follow up on any outstanding accounts.
I have a subform for tblFollowUp (frmFollowUp subform) which I have on a subform. I want to select the lstConsultDate lstbox and automatically populate a value in the frmFollowUp suborm's textbox.
There is a checkbox in the tblFollowUp called PaymentReceived. When this is checked I would like the lstConsultDate to no longer display the date. I know there are alot of questions here but I would appreciate any help
Thanks
SELECT tblFollowUp.Consult, tblFollowUp.RequiresFollow
The main form has an unbound listbox (lstConsultDate) that displays tblConsult, this is bound to another lstbox from tblClient. When I select the Client, it displays the consultations for that client that do not have a payment attached. I am using this to follow up on any outstanding accounts.
I have a subform for tblFollowUp (frmFollowUp subform) which I have on a subform. I want to select the lstConsultDate lstbox and automatically populate a value in the frmFollowUp suborm's textbox.
There is a checkbox in the tblFollowUp called PaymentReceived. When this is checked I would like the lstConsultDate to no longer display the date. I know there are alot of questions here but I would appreciate any help
Thanks
SELECT tblFollowUp.Consult, tblFollowUp.RequiresFollow
SELECT tblFollowUp.Consult, tblFollowUp.RequiresFollow
can more than one item be selected in your listbox? If so, this adds complexity to what you're trying to do.
ASKER
It is coming up with an error on the unbound txtbox on the main form
ASKER
Yes it displays all of the existing consultations that do not have a payment.
<There is a checkbox in the tblFollowUp called PaymentReceived. When this is checked I would like the lstConsultDate to no longer display the date>
add the to the row source of listbox
where tblFollowUp.PaymentReceive
add the to the row source of listbox
where tblFollowUp.PaymentReceive
johnberniejones,
what is the row source of your listbox,
post it here
what is the row source of your listbox,
post it here
if you find errors....let us know what they are...and what you've done to get it...we can't see your screen.
ASKER
The code for lstConsultDate is
SELECT tblConsult.Client, tblConsult.ConsultID, tblConsult.ConsultDate, tblFollowUp.PaymentReceive
SELECT tblConsult.Client, tblConsult.ConsultID, tblConsult.ConsultDate, tblFollowUp.PaymentReceive
change it to this, so PaymentReceived that is true will not show
SELECT tblConsult.Client, tblConsult.ConsultID, tblConsult.ConsultDate, tblFollowUp.PaymentReceive
SELECT tblConsult.Client, tblConsult.ConsultID, tblConsult.ConsultDate, tblFollowUp.PaymentReceive
ASKER
The lstConsultDate doesn't display any values. If I remove the PaymentReceived = False from the query it displays the values
<I want to select the lstConsultDate lstbox and automatically populate a value in the frmFollowUp suborm's textbox>
what value you want show in the textbox?
you can use a dlookup function
in the afterupdate or click event of the lstConsultDate
private sub lstConsultDate_afterUpdate
me.frmFollowUp.form.textbo
what value you want show in the textbox?
you can use a dlookup function
in the afterupdate or click event of the lstConsultDate
private sub lstConsultDate_afterUpdate
me.frmFollowUp.form.textbo
ASKER
Is this because that only some of the consultations have a FollowUpID? I
<The lstConsultDate doesn't display any values. If I remove the PaymentReceived = False from the query it displays the values>
then PaymentReceived must all be true or checked
then PaymentReceived must all be true or checked
ASKER
I was able to get the hidden text box to display the value selected in the list box, but if I add the paymentreceived = True, then the lstConsultDate does not display any results.
How do I get the hidden textbox to enter into the subform?
How do I get the hidden textbox to enter into the subform?
johnberniejones,
can you post sample data from
tblFollowUp
and
tblConsult
or better zip your db and upload here
http://www.ee-stuff.com/login.php
can you post sample data from
tblFollowUp
and
tblConsult
or better zip your db and upload here
http://www.ee-stuff.com/login.php
ASKER
I have used a front and back end... I'll attach both
ASKER
The file is uploaded under ID:22425378
what is the name of the bound Text box on subform ?
ASKER
Consult
I've got this opened and right now, the recordsource for the subform is using the ID from the main form.
Reading your question again, you're trying to have that subform changed based on the selection in the listbox, rather than the Main Form's ID?
Is that what the query you pasted into your Q is for? to be the recordsource for the subform?
Reading your question again, you're trying to have that subform changed based on the selection in the listbox, rather than the Main Form's ID?
Is that what the query you pasted into your Q is for? to be the recordsource for the subform?
ASKER
Yes, the recordsource for the Subform should be lstconsultdate
ok....here's what I suggest
Take the criteria out completely.
then add this to the main form's code mod.
Private Sub lstConsultDate_Click()
Me.frmClientFollowUpSubfor
Me.frmClientFollowUpSubfor
End Sub
and make this the recordsource for that little subform
SELECT tblFollowUp.Consult, tblFollowUp.RequiresFollow
FROM tblFollowUp INNER JOIN tblConsult ON tblFollowUp.Consult = tblConsult.ConsultID
WHERE (((tblFollowUp.PaymentRece
Take the criteria out completely.
then add this to the main form's code mod.
Private Sub lstConsultDate_Click()
Me.frmClientFollowUpSubfor
Me.frmClientFollowUpSubfor
End Sub
and make this the recordsource for that little subform
SELECT tblFollowUp.Consult, tblFollowUp.RequiresFollow
FROM tblFollowUp INNER JOIN tblConsult ON tblFollowUp.Consult = tblConsult.ConsultID
WHERE (((tblFollowUp.PaymentRece
ASKER
When I go to open the form it is now asking to enter a paremter value
Forms!frmFollowUp!lstClien
Forms!frmFollowUp!lstClien
I'm working on other methods too...the hidden text box for one.
I'm curious though...as I scroll through records on the main form...the listboxes don't change....Aren't they supposed to? It may be because your payment table didn't get included with the BE. Not sure.
I'm curious though...as I scroll through records on the main form...the listboxes don't change....Aren't they supposed to? It may be because your payment table didn't get included with the BE. Not sure.
it should be
Private Sub lstConsultDate_Click()
Me.txtConsultDateHidden.Va
Me.[frmFollowUp subform].Form.Filter = "[Consult]= " & Me.lstConsultDate.Column(1
Me.[frmFollowUp subform].Form.FilterOn = True
End Sub
and the record source
SELECT tblFollowUp.Consult, tblFollowUp.RequiresFollow
FROM tblFollowUp INNER JOIN tblConsult ON tblFollowUp.Consult = tblConsult.ConsultID
WHERE (((tblFollowUp.PaymentRece
>>When I go to open the form it is now asking to enter a paremter value
Forms!frmFollowUp!lstClien
not sure why. But it may be moot...I'll wait until you answer my last Q
Forms!frmFollowUp!lstClien
not sure why. But it may be moot...I'll wait until you answer my last Q
ASKER
I noticed that when I select the client, the lstConsultDate value don't change
ASKER
Sorry, it does change when I select the client. The value in the frmFollowUp SUbform is not entering a new value however.
that's correct. Also, when you move from one record to another using your main form's navigation buttons, your listbox's don't update. In this copy that I'm working on, I put a requery in the form's On_Current event for the List32 listbox and the lstMedicalHistory listbox. I tried putting it in for the lstConsultDate but it crashed Access.....so I'm trying to figure out why.
These listboxes are all dependent on the main form's ClientID...so each time it changes...they should update.
The subform relies on the lstConsultDate listbox's Selected Item. So it stays blank until you select something. So I put the requery for the subform in that listbox's on_click event. That seems to work ok, but there isn't any data for these records yet.
J
These listboxes are all dependent on the main form's ClientID...so each time it changes...they should update.
The subform relies on the lstConsultDate listbox's Selected Item. So it stays blank until you select something. So I put the requery for the subform in that listbox's on_click event. That seems to work ok, but there isn't any data for these records yet.
J
frmFollowUp SUbform is not entering a new value however.
do you mean you want a data entry for new record in the subform and not to edit the records?
do you mean you want a data entry for new record in the subform and not to edit the records?
jeff,
i think you are working on a different form
i think you are working on a different form
i'm working on the frmClient
s/b
frmFollowUp
frmFollowUp
Here's the issue.
You want the subform to react to the value in the listbox. But because of the nature of a listbox....there is no actual value until you select something. It's at that point you have a value to use to filter the subform.
The listboxes all have a SQL recordsource that use the ClientID from the main form as their criteria. That's fine...except that when you move from record to record, they aren't getting requeried. Hence my comment above about the form_current event.
I'm trying still to understand the sequence of events that need to take place to get to the subform and have it do what you want it to do. It seems that all you want is that when you select something from the lstConsultDate listbox, you want the subform to respond.
That being said...I've got all of that working except there's not any data in the subform for the ConsultID that I've selected. AND, I think my farting around with the listbox, I've somehow corrupted it, so I can't requery it using the Form_Current's event without crashing the app.
Other than that...it all seems to go ok.
J
You want the subform to react to the value in the listbox. But because of the nature of a listbox....there is no actual value until you select something. It's at that point you have a value to use to filter the subform.
The listboxes all have a SQL recordsource that use the ClientID from the main form as their criteria. That's fine...except that when you move from record to record, they aren't getting requeried. Hence my comment above about the form_current event.
I'm trying still to understand the sequence of events that need to take place to get to the subform and have it do what you want it to do. It seems that all you want is that when you select something from the lstConsultDate listbox, you want the subform to respond.
That being said...I've got all of that working except there's not any data in the subform for the ConsultID that I've selected. AND, I think my farting around with the listbox, I've somehow corrupted it, so I can't requery it using the Form_Current's event without crashing the app.
Other than that...it all seems to go ok.
J
yep...wrong form...but still it has issues that needed some fix'in.
Do you have this database downloaded Cap?
Do you have this database downloaded Cap?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm adding new records, however once I have checked the "PaymentReceived" checkbox the consult should no longer be displayed in the lstConsultDate. So I can add comments, tick other checkboxes. Once the PaymentReceived is checked the consult is no longer displayed. I hope this makes sense.
add this codes
Private Sub lstConsultDate_AfterUpdate
Me.[frmFollowUp subform].SetFocus
DoCmd.GoToRecord , , acNewRec
Me.[frmFollowUp subform].Form.consult = Me.lstConsultDate.Column(1
End Sub
Private Sub lstConsultDate_AfterUpdate
Me.[frmFollowUp subform].SetFocus
DoCmd.GoToRecord , , acNewRec
Me.[frmFollowUp subform].Form.consult = Me.lstConsultDate.Column(1
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Forced accept.
Computer101
EE Admin
Computer101
EE Admin
this part won't work. You'll need to create an unbound textbox and use it. You'll need to set it's value = to the selection in your listbox. You can size it and hide it behind another control if you don't want it seen.
=[Forms]![frmFollowUp]![my