HOW Do I: use Coding to Get a Value from 1 Table...

[Adigga1]

Hello,

Are there any codes or commands that can look up and get a value in 1 table using a combo box on a Form then pass it and save to another table after it has been selected?

thanks
A...

 

[HiTechCoach]

??? ????

Can you explain what you are needed to do?

 

[ajetrumpet]

YES THERE is!

 

[Adigga1]

??? ????

Can you explain what you are needed to do?

Sorry about my vague question; I will also illustrate with a copy of the broken down database.

Issue:

On my Form (F_Pay) which was created from table (T_Payments) uses the unbound field (Case MRN to lookup the AcctNum of T_Patient.

What I'm attempting to achieve:
Once I select a Case MRN: from the combo box, I would like that value to be written into the AcctNnum field of T_Payments along with the associated entered fields of the form as 1 record.

Hope this clarified my issue?
Thank you

 

[JANR]

Why not bind combo7 to AcctNnum?

JR

 

[Adigga1]

Why not bind combo7 to AcctNnum?

JR

Should I bind the AcctNnum of the T_Patient or T_Payment?
Can you illustrate with code? I'm still a little new at this...
thanks

A...

 

[JANR]

The form you provided is bound to the table T_Payment, so by setting the Control Source of combo7 to AcctNnum it is bound to table and whenever you change the value og your combo it will store the value into T_Payment.

JR

 

[Adigga1]

The form you provided is bound to the table T_Payment, so by setting the Control Source of combo7 to AcctNnum it is bound to table and whenever you change the value og your combo it will store the value into T_Payment.

JR

Thanks for the reply JR, Again please bare with me... the AcctNnum in the T_Payment Table looks up the values in T_Patient. So what I did was to put combo7 to lookup Row source Tpatient.AcctNnum, apparantly this is wrong?
So, should I just change the T_payment Table field (AcctNnum) to a text box and use =[T_Patient]![AcctNnum] as Control source on Combo7?

thanks

 

[JANR]

No, use combo7 rowsource as it is and just bind it to T_payment.

JR

 

[Adigga1]

No, use combo7 rowsource as it is and just bind it to T_payment.

JR

OK, I tried that option, but one I added =[T_Payment]![AcctNnum] as the Control Source, I"m being blocked on the drop down on the form, so u can see the values but cannot select them. even when i converted the T_Payment AcctNnum back to a text box on the table's end....

Here is the form with the edits....thanks

 

[JANR]

Look at my last post and just do that aka.

Control Source -> AcctNnum

And nothing more.

JR

 

[Adigga1]

Look at my last post and just do that aka.

Control Source -> AcctNnum

And nothing more.

JR

ok I will try that and update..thank you again for your patience with this

 

[Adigga1]

Thanks again JANR, here's what occurs after the bind:

the values do not line up when i toggle between values

 

[Adigga1]

Here is the second image after toggle:

and here is my Form code"

Option Compare Database
Private Sub cmbCaseNum_AfterUpdate()
'Me.Form.Filter = "CaseMRN=" & Me.cmbCaseNum.Column(0)
'Forms![$mainmenu]![F_History_By_Store_Final].Form.Filter = & _
'"Store_ID=" & Me.Store_Selection.Column(0) & _
'" and year=" & Chr(34) & Me.Year_Selection & Chr(34)
'Form_F_History_By_Store_Final.Form.FilterOn = True
'Me.Form.FilterOn = True
End Sub
Private Sub cmbCaseNum_BeforeUpdate(Cancel As Integer)
End Sub
Private Sub cmbCaseNum_Change()
'txtCasebilledDate.SetFocus
'txtCasebilledDate.Text = ""
'txtExpectedReimAmount.SetFocus
'txtExpectedReimAmount.Locked = False
' txtExpectedReimAmount.Text = ""
' txtExpectedReimDate.SetFocus
' txtExpectedReimDate.Text = ""
'txtAmountReceived.SetFocus
'txtAmountReceived.Text = ""
'txtSibley.SetFocus
'txtSibley.Text = ""
'txtScottishRite.SetFocus
'txtScottishRite.Text = ""
'txtTEC.SetFocus
'txtTEC.Text = ""
'txtECC.SetFocus
'txtECC.Text = ""
'txtAPC.SetFocus
'txtAPC.Text = ""
'txtEPG.SetFocus
'txtEPG.Text = ""
'txtEgleston.SetFocus
'txtEgleston.Text = ""
'txtProviderPaymentDate.SetFocus
'txtProviderPaymentDate.Text = ""
'txtReimbursementBalance.SetFocus
'txtReimbursementBalance.Text = ""

' Dim strSQL As String
' strSQL = "SELECT * FROM T_Payment WHERE AcctNnum = '" & cmbCaseNum.Text & "';"
' Me.Form.RecordSource = strSQL

End Sub
Private Sub Combo7_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[AcctNnum] = '" & Me![Combo7] & "'"
Me.Bookmark = rs.Bookmark
End Sub
Private Sub Detail_Click()
End Sub

 

[JANR]

On my Form (F_Pay) which was created from table (T_Payments) uses the unbound field (Case MRN to lookup the AcctNum of T_Patient.

OK normal lookup.

Once I select a Case MRN: from the combo box, I would like that value to be written into the AcctNnum field of T_Payments along with the associated entered fields of the form as 1 record.

??? dosen't compute. You say that you look in T_Patient for matching AcctNum that is already in T_Payments?? and you want to write back to T_Payments the same value For that's what I read.

If your intent is to add records in T_Payments which reletes to T_Patient, then perhaps a Form with subform is a better way to do this.

Base the mainform on table patient and add a subform on to it that's based on T_Payments. You link them together by AcctNnum, that way you keep refrential integrety and don't get orphan records in T_Payments.

Hope this guide you further.

JR

 

[Adigga1]

OK normal lookup.



??? dosen't compute. You say that you look in T_Patient for matching AcctNum that is already in T_Payments?? and you want to write back to T_Payments the same value For that's what I read.

If your intent is to add records in T_Payments which reletes to T_Patient, then perhaps a Form with subform is a better way to do this.

Base the mainform on table patient and add a subform on to it that's based on T_Payments. You link them together by AcctNnum, that way you keep refrential integrety and don't get orphan records in T_Payments.

Hope this guide you further.

JR

Those data in the T_payment were test data that i entered and I wanted to enter new records using the AcctNnum from the Patient table..

OK, i may look into that option then. Thank you for all your assistance and patience with this issue

Regards,
A..