Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2016
    Posts
    3

    Answered: Nested Forms & VBA Code Issue

    Basically, the DB I am working on is to track the service record of particular appliances.....for the
    context of this problem, there are three main tables:
    -Customers
    -Machine Details
    -Service Record

    I have created a nested form which has the Machine Details and a sub form for the Service Record (these are linked by serial number).....this form works fine and pulls up all the relevant services for the particular serial#..great.

    Within the Service Record sub form....I have some code on the Lost Focus event for the 'Service_Date' control, which basically takes the last service date and places it into the 'Last Service Date' field on the parent form (i.e. Machine Details)......the code basically finds the DMax on the 'service date' field where the serial# matches that of the serial# on the parent form...see below:

    Private Sub Service_Date_LostFocus()

    Dim Ldate As Date
    Ldate = DMax("[Service Date]", "Service_Record", "[Serial Number] = Forms!Machine_Details1![Serial Number]")

    Forms!Machine_Details1![Last Service Date] = Ldate

    End Sub

    This code works fine when I open the Machine Details form (i.e the nested form)...great

    However.....when I try to nest the Machine Details form within the Customers form.....the form works and pulls up all the correct relevant data.......but the code within the Service Record form does not.......I get an error saying that it cannot find the Machine Details form ?!

    Since it works fine when I open the Machine Details form......I cant figure out why it 'cant find it' when I nest it further into the Customers form??

    I feel like it is a simple issue...but maybe I'm too close to see it.....would appreciate any help.

    Thanks a lot

  2. Best Answer
    Posted by healdem

    "if the value you are supplying to query or domain function is a text/string/alphanumeric it MUST be delimited with either ' or "
    Code:
     DMax("[Service Date]", "Service_Record", "[Serial Number] = '" & Me.Parent![Serial Number] & "'" )
    FYI if its a date it must be in ISO yyyy/mm/dd or US mm/dd/yyyy but not (virtually) everywhere else dd/mm/yyyy AND must be delimited by #

    a number should be of the smae type eg if its one of the integer column it should be an integer value (ie 123 not 122.75 or 123.0), likewise a decimal value should be a decimal 123.0 not 123"


  3. #2
    Join Date
    Mar 2009
    Posts
    5,440
    Provided Answers: 14
    Have a nice day!

  4. #3
    Join Date
    Mar 2016
    Posts
    3

    Syntax Issue now

    thanks for the info...

    I tried changing the code to below:

    Private Sub Service_Date_LostFocus()

    Dim Ldate As Date
    Serial = Me.Parent![Serial Number]
    MsgBox (Serial)
    Ldate = DMax("[Service Date]", "Service_Record", "[Serial Number] = " & Me.Parent![Serial Number])
    MsgBox (Ldate)
    Me.Parent![Last Service Date] = Ldate

    Me.Parent![Next Service Date] = (Ldate + 90)


    End Sub

    ....but now I'm getting the below syntax error:

    Run Time Error (3075)
    Syntax Error (missing operator) in query expression '[Serial Number] = 204ELB5800'.

    ( The serial# 204ELB5800 is the correct one, so the Me.Parent![Serial Number] is working correctly )

    I'm sure it is a simple syntax issue...but its pissing me off !!

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if the value you are supplying to query or domain function is a text/string/alphanumeric it MUST be delimited with either ' or "
    Code:
     DMax("[Service Date]", "Service_Record", "[Serial Number] = '" & Me.Parent![Serial Number] & "'" )
    FYI if its a date it must be in ISO yyyy/mm/dd or US mm/dd/yyyy but not (virtually) everywhere else dd/mm/yyyy AND must be delimited by #

    a number should be of the smae type eg if its one of the integer column it should be an integer value (ie 123 not 122.75 or 123.0), likewise a decimal value should be a decimal 123.0 not 123
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Mar 2016
    Posts
    3

    Rock on!

    Thanks for the help....it's working now!

    I have a feeling that I'll have some other issues to run by you....but thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •