Solved

Getting an error when form opens

Posted on 2016-07-21
9
16 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 142

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 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 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
 

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

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

Expert Comment

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

Expert Comment

by:Rey Obrero
ID: 41723237
is the form opened in edit or add mode?
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 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 119

Accepted Solution

by:
Rey Obrero earned 250 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now