Solved

Getting an error when form opens

Posted on 2016-07-21
9
23 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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 38

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

695 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