?
Solved

Getting an error when form opens

Posted on 2016-07-21
9
Medium Priority
?
28 Views
Last Modified: 2016-07-21
I am getting an error when trying to open a form and populate a date field using the following code in the onopen event of the form:

Me.txtDateChecked = DMax("[DateChecked]", "[Treatment]", "[MemberID] = " & Forms!frmMemberTreatments!txtMemberID.Value & " ")


What am I doing wrong?
0
Comment
Question by:SteveL13
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41723156
Hi,

It may be possible that the text box txtMemberID does not have a value when the code executes.

What is the error ?

Can you run the code after the form has fully loaded to see if the onload is causing the problem?

Regards,
Jonathan
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41723157
what error?
memberid numeeic or text?

i suspect text

Me.txtDateChecked = DMax("[DateChecked]", "[Treatment]", "[MemberID] = '" & Forms!frmMemberTreatments!txtMemberID.Value & "' ")

but this requires more stuff to avoid sql injection issues
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 41723166
is the MemberID number or text

if text
Me.txtDateChecked = DMax("[DateChecked]", "[Treatment]", "[MemberID] = '" & Forms!frmMemberTreatments!txtMemberID.Value & "' ")

and use the LOAD event of the form.

where is the textbox "txtDateChecked " located ? in a subform?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:SteveL13
ID: 41723168
Sorry... the error is "You can't assign a value to this object"

The whole code is:

    If IsNull(Me.txtDateChecked) Then
        Me.txtDateChecked = DMax("[DateChecked]", "[Treatment]", "[MemberID] = " & Forms!frmMemberTreatments!txtMemberID.Value & " ")
    End If

Open in new window

0
 

Author Comment

by:SteveL13
ID: 41723171
Rey,
MemberID is number
The textbox "txtDateChecked  is on the form itself.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41723172
means you have no current or no selected record yet.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41723237
is the form opened in edit or add mode?
0
 
LVL 41

Assisted Solution

by:PatHartman
PatHartman earned 1000 total points
ID: 41723238
In what event are you doing the assignment?

When you open a form from another form and you want to use data from the first form to populate fields of the second form, the correct event to use is the BeforeInsert event.  You don't want to populate the data until the user himself dirties the record.  The BeforeInsert event runs only once and it runs as soon as the form has been dirtied but only for new records so You wouldn't have to worry about modifying the value in an existing record.  Many people make the mistake of trying to populate fields as soon as the form opens but the recordset has not yet been loaded so bound fields are not yet accessible.  The other reason for using the BeforeInsert event is because if you allow the user to enter multiple records, the BeforeInsert event will fire for each of them so every record that gets inserted will have the foreign key correctly populated.
1
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 41723243
and use this code, no need for the trailing & " "

    If IsNull(Me.txtDateChecked) Then
        Me.txtDateChecked = DMax("[DateChecked]", "[Treatment]", "[MemberID] = " & Forms!frmMemberTreatments!txtMemberID.Value)
    End If
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question