Full Version: Label to show info from another table.
UtterAccess Forums > Microsoft® Access > Access Forms
cowannbell
I have a DB with a form called Correspondence Main. On the Correspondence main, I want to view the contents of the New ARS # from another table, if there is one. Below is the code that I currently have but it doesn't know what New Fee Sch to show because the tables are linked. They should link based on Corr ID & ID but I don't know how to tell it to do that. Can someone please advise.
Thanks,
If DCount("*", "[tblARS]", "[Corr ID] = " & Nz(Forms![Correspondence Main]![ID], 0)) > 0 Then
Me.LabelARS.Caption = "New ARS#" & [tblARS].[New Fee Sch]
Me.LabelARS.Visible = True
Else
Me.LabelARS.Visible = False
End If
End Sub
jzwp11
You could try a DLookUp. From what event is this code executing?
If DCount("*", "[tblARS]", "[Corr ID] = " & Nz(Forms![Correspondence Main]![ID], 0)) > 0 Then
Me.LabelARS.Caption = "New ARS#" & Dlookup("[New Fee Sch]","[tblARS]", "[Corr ID] = " & Forms![Correspondence Main]![ID])
Me.LabelARS.Visible = True
Else
Me.LabelARS.Visible = False
End If
cowannbell
That didn't work. It's on the on current event of the Correspondence Main form. Do I need to use Me?
jzwp11
I made up a dummy database real quick and copied your code over and it worked fine. Are you getting any errors?
cowannbell
You're correct. My fee sch name was actually ARS New Fee Sch. It works now.
Thanks,
cowannbell
Is there a way to make this a searchable field? The whole reason to add this to the main form, is so that the user can find the record that has a certain ARS New Fee Sch #. Is there a better way to go about that?
jzwp11
I'm not sure I understand your question. Are you saying that the user has the ARS New Fee Sch # and wants to find out who it is related to? If your table structure is set up correctly then you should be able to prompt the user for the # and return the form with the record that is tied to that ARS New Fee Sch#. So I guess the next step is taking a look at your table structure. Can you provide that?
cowannbell
There are several forms in this database and several tables. The main form is attached to the main table and then there is a ARS form. The ars table is linked to the main table by the Corr ID #. Not all main records will have a ARS record but every ARS record will have a main record.
The user wants to be on the main page of the databse and find the main record that has an ars record with a certain fee sch number.
Odon't know what is the best way to do that.
jzwp11
Is the fee sch number unique? In other words, will it only exist in 1 ars record or can the fee sch number be found in many ars records?
I would use an unbound form. On that form, I would place a combo box control that brings in all of the available fee sch numbers and a command button. You can use the command button wizard to open your main form to the record with the selected fee sch number. See the attached DB for a simple example.
cowannbell
Where does the Myform come into play? Didn't see it attached to anything.
The fee sch should be unique but not guaranteed.
This is a little bit more than I would like to have to do.
I would really like it, if when they cick on the find command button, that it would search the ars table also and find the record but it doesn't seem to do that.
THere is the code behind the find command button. It's simply just a find option.
Private Sub Command71_Click()
On Error GoTo Err_Command71_Click
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_Command71_Click:
Exit Sub
Err_Command71_Click:

Resume Exit_Command71_Click

End Sub
jzwp11
The form MyForm was for another thread on the forum.
You could set up your main form with a subform tied to the ARS table then when you use the combo box & find button it will show the main record with the detail from the ARS table.
cowannbell
I'll think about it. I was hoping that I could find something much easier for the user.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.