Solved

Getting an error when form opens

Posted on 2016-07-21
9
20 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 36

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

837 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