Function to pull records based on field in subform

  • Thread starter Thread starter Andrea Odell via AccessMonster.com
  • Start date Start date
A

Andrea Odell via AccessMonster.com

I have a subform titled subfrmCustomerPtsTotals. It resides on the main
form titled frmLogin. The subfrmCustomerPtsTotals contains only two fields
Product Name and Points. I need a function that will be called when the
Main Form is opened. The function will pull the Points for each Product
Name and determine if the Points are > 11. If yes, a message box will
appear.

I can't figure out how to call a function from a main form that references
items in a subform. Nor am I completely sure about where the code for the
function should be placed. I have limited vb programming skills and
limited skills with Access. Thus, I'll need detailed assistance. I pasted
the current state of my function below.

Public Sub Redeem()

Dim Points As String
Dim Msg As String
Dim ans As Integer

Points = Me!ubfrmCustomerPtsTotals.frmLogin.txtSumOfPointValue1.Text
Msg = "Customer has earned a FREE " & Me!
subfrmCustomerPtsTotals.frmLogin.txtProductName.Text & ". Would you like to
redeem it?"
If Points >= 11 Then
ans = MsgBox(Msg, vbYesNo, "Free")
End If

End Sub

THANK YOU FOR YOUR ASSISTANCE.
 
Andrea,

Modify your code as follows:

Dim Points As Long
Dim Msg As String
Dim ans As Integer

Points = Me.subfrmCustomerPtsTotals.txtSumOfPointValue1
Msg = "Customer has earned a FREE "
Msg = Msg & Me.subfrmCustomerPtsTotals.txtProductName
Msg = Msg & ". Would you like to redeem it?"
If Points >= 11 Then
ans = MsgBox(Msg, vbYesNo, "Free")
End If

Note changes:
1. Even though txtSumOfPointValue1 is a textbox, the number of points is
a numeral; declare variable Points as Long to treat it as a number.
2. Reference to objects on subform is generally like:
Me.SubformName.ObjectName
where Me = this form, when the code is in the form's own module
(likewise for a report).
3. No need to refer to a control's Text property; it's what is
referenced by default, when no properrty is specified; what's more,
referencing the Text property when the control doesn't have the focus
will produce an error.

The code should go in the form's On Open event: while in design view,
select the Events tab in the Properties window for the whole form, place
the cursor next to On Open, click on the little button that appears on
the right and choodse Code Builder; you will be taken to the VBA editor
with a header and footer for the event already there, which looks like:

Private Sub Form_Open(Cancel As Integer)

End Sub

just paste the code above between those lines.

Note: your code will display the message box, but no action is taken
based on the user's selection. To do that, the code should be extended
like this:

Dim Points As Long
Dim Msg As String
Dim ans As Integer

Points = Me.subfrmCustomerPtsTotals.txtSumOfPointValue1
Msg = "Customer has earned a FREE "
Msg = Msg & Me.subfrmCustomerPtsTotals.txtProductName
Msg = Msg & ". Would you like to redeem it?"
If Points >= 11 Then
ans = MsgBox(Msg, vbYesNo, "Free")
Select Case ans
Case vbYes
'code for redemption
Case vbNo
'code for no redptn, or no code => do nothing
End Select
End If

HTH,
Nikos
 
I modifed the code as you suggested and now I'm receiveing an error that
the "Method or data member not found" on the
Me.subfrmCustomerPtsTotals.txtSumOfPointValue1 statement. Should this
code be placed on the Main form's on open event or on the subform's on open
event? I've tried both and neither work. Thanks again for your
assistance.
 
Never mind. I figured it out! Thanks for your help.
 
Back
Top