Accessing Row from 2nd Query in Form using On Current

[matthewtckr]

I have a form that will be used to track members in an organization,
and I would like a Textbox to be visible in the main section, but I use
subforms for all of the other information.

The query Dues_Payments contains the following fields:
Member_ID
MaxDues

I set the form's On Current Event Procedure to

Private Sub Form_Current()
Dim strSQL As String
strSQL = "SELECT * " & _
"FROM [Most Recent Dues] WHERE [Most Recent Dues]![Member_ID] =
[Me]![Member_ID]![Value];"
Me.Text88.Value = DLookup("MaxDues", strSQL)
End Sub

but I get error 3078, saying that the database engine couldn't find the
input table or query.

Disclaimer: I'm not anywhere near proficient at VB, but mainly stick
to C, Java, and PHP.

 

[Marshall Barton]

I have a form that will be used to track members in an organization,
and I would like a Textbox to be visible in the main section, but I use
subforms for all of the other information.

The query Dues_Payments contains the following fields:
Member_ID
MaxDues

I set the form's On Current Event Procedure to

Private Sub Form_Current()
Dim strSQL As String
strSQL = "SELECT * " & _
"FROM [Most Recent Dues] WHERE [Most Recent Dues]![Member_ID] =
[Me]![Member_ID]![Value];"
Me.Text88.Value = DLookup("MaxDues", strSQL)
End Sub

but I get error 3078, saying that the database engine couldn't find the
input table or query.

Disclaimer: I'm not anywhere near proficient at VB, but mainly stick
to C, Java, and PHP.

Disclaimer: that code appears to be embedded in some kind of
script that I am not qualified to comment about.

You can not use DLookup with an SQL statement. I thing(?)
this may be equivalent to what you are trying to do.

Private Sub Form_Current()
Me.Text88 = DLookup("MaxDues", "Most Recent Dues", _
"[Most Recent Dues]!Member_ID = " & Me!Member_ID)
End Sub

Note that if Text88 is a bound text box, that code will
dirty the record even when the user does not intend to make
a change. It is extremely bad practice to modify records
unnecessarily.

If the text box is not bound, then there is no need for the
Current procedure to do anything. Just set the text box's
Control Source to the expression:
=DLookup("MaxDues", "Most Recent Dues", "[Most Recent
Dues]!Member_ID = " & Me!Member_ID)

 

[matthewtckr]

I had seen those before, but could never find any information about how
they worked. Thank you so much!

Matt

P.S. Apparently the automation part didn't like the Me part, but that
was a quick fix of [Forms]![Members]!Member_ID

I have a form that will be used to track members in an organization,
and I would like a Textbox to be visible in the main section, but I use
subforms for all of the other information.

The query Dues_Payments contains the following fields:
Member_ID
MaxDues

I set the form's On Current Event Procedure to

Private Sub Form_Current()
Dim strSQL As String
strSQL = "SELECT * " & _
"FROM [Most Recent Dues] WHERE [Most Recent Dues]![Member_ID] =
[Me]![Member_ID]![Value];"
Me.Text88.Value = DLookup("MaxDues", strSQL)
End Sub

but I get error 3078, saying that the database engine couldn't find the
input table or query.

Disclaimer: I'm not anywhere near proficient at VB, but mainly stick
to C, Java, and PHP.

Disclaimer: that code appears to be embedded in some kind of
script that I am not qualified to comment about.

You can not use DLookup with an SQL statement. I thing(?)
this may be equivalent to what you are trying to do.

Private Sub Form_Current()
Me.Text88 = DLookup("MaxDues", "Most Recent Dues", _
"[Most Recent Dues]!Member_ID = " & Me!Member_ID)
End Sub

Note that if Text88 is a bound text box, that code will
dirty the record even when the user does not intend to make
a change. It is extremely bad practice to modify records
unnecessarily.

If the text box is not bound, then there is no need for the
Current procedure to do anything. Just set the text box's
Control Source to the expression:
=DLookup("MaxDues", "Most Recent Dues", "[Most Recent
Dues]!Member_ID = " & Me!Member_ID)

 

[Marshall Barton]

Of course, Me is only valid in a class module. My
Copy/Paste mistake.

If the DLookup text box is in the same form as the Member_ID
text box, you do not need to use the Forms!Members part of
the reference.

=DLookup("MaxDues", "Most Recent Dues", "[Most Recent
Dues]!Member_ID = " & Member_ID)
--
Marsh
MVP [MS Access]

I had seen those before, but could never find any information about how
they worked. Thank you so much!

Matt

P.S. Apparently the automation part didn't like the Me part, but that
was a quick fix of [Forms]![Members]!Member_ID

I have a form that will be used to track members in an organization,
and I would like a Textbox to be visible in the main section, but I use
subforms for all of the other information.

The query Dues_Payments contains the following fields:
Member_ID
MaxDues

I set the form's On Current Event Procedure to

Private Sub Form_Current()
Dim strSQL As String
strSQL = "SELECT * " & _
"FROM [Most Recent Dues] WHERE [Most Recent Dues]![Member_ID] =
[Me]![Member_ID]![Value];"
Me.Text88.Value = DLookup("MaxDues", strSQL)
End Sub

but I get error 3078, saying that the database engine couldn't find the
input table or query.

Disclaimer: I'm not anywhere near proficient at VB, but mainly stick
to C, Java, and PHP.

Disclaimer: that code appears to be embedded in some kind of
script that I am not qualified to comment about.

You can not use DLookup with an SQL statement. I thing(?)
this may be equivalent to what you are trying to do.

Private Sub Form_Current()
Me.Text88 = DLookup("MaxDues", "Most Recent Dues", _
"[Most Recent Dues]!Member_ID = " & Me!Member_ID)
End Sub

Note that if Text88 is a bound text box, that code will
dirty the record even when the user does not intend to make
a change. It is extremely bad practice to modify records
unnecessarily.

If the text box is not bound, then there is no need for the
Current procedure to do anything. Just set the text box's
Control Source to the expression:
=DLookup("MaxDues", "Most Recent Dues", "[Most Recent
Dues]!Member_ID = " & Me!Member_ID)