Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50

    Unanswered: Open form based on Query null or not null

    I am having trouble with a form that is based on a query. I have a contacts database with a query that tells me if there is anyone on the contacts lists that has a birthday within the next 10 days.

    As it is now, I have the form (based on the birthday query) pop up when the database is first opened. What I would like to do is only have the form pop up only if there is data in the query (meaning that the values in the query are not null). As it is now the form opens up blank because there are no birthdays in the next 10 days.

    Please tell me if you need any more info.

    Thanks Brian

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Use the DCount function for now if the query have record, and the IsEmpty function to now if value return for the DCount function is empty or not.
    Saludos
    Norberto

  3. #3
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    Originally posted by Norberto
    Use the DCount function for now if the query have record, and the IsEmpty function to now if value return for the DCount function is empty or not.
    Can you give me more detail. I'm not real sure what you are talking about. Thanks again. Brian

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    I send an sample with the Dsum function but its the same.

    strfecha = Format(Date, "mm/dd/yyyy")
    Haber = DSum("[TOTMOV]", "Tbl ED Publicaciones", " [CODEDI] = " & Me![Editorial] & " AND [FECMOV]<= #" & strfecha & "# and [CODCOM] = '" & "IN" & "' or [CODEDI] = " & Me![Editorial] & " AND [FECMOV]<= #" & strfecha & "# and [CODCOM] = '" & "SO" & "' or [CODEDI] = " & Me![Editorial] & " AND [FECMOV]<= #" & strfecha & "# and [CODCOM] = '" & "ND" & "'")
    Debe = DSum("[TOTMOV]", "Tbl ED Publicaciones", " [CODEDI] = " & Me![Editorial] & " AND [FECMOV]<= #" & strfecha & "# and [CODCOM] = '" & "PA" & "' or [CODEDI] = " & Me![Editorial] & " AND [FECMOV]<= #" & strfecha & "# and [CODCOM] = '" & "DV" & "' or [CODEDI] = " & Me![Editorial] & " AND [FECMOV]<= #" & strfecha & "# and [CODCOM] = '" & "NC" & "'")
    Haber = Nz(Haber, 0)
    Debe = Nz(Debe, 0)
    If IsEmpty(Haber) = True Then
    Haber = 0
    End If
    If IsEmpty(Debe) = True Then
    Debe = 0
    End If
    Me!Saldo = Haber - Debe

    I use a Table but you can use a Query Name
    Saludos
    Norberto

  5. #5
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    I don't mean to be a pain but where would I put this code? I'm still new at this. Thanks

  6. #6
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    If your problem still yet post an sample base in Access 97 format and i see what i can do.
    Saludos
    Norberto

  7. #7
    Join Date
    Dec 2002
    Location
    Columbus, GA
    Posts
    81
    Heres how I solved the problem. I have a similar query that does the same.

    I created a form that is based on that query witrh the birthdys awithin 10 days. Save it and then in your autoexec macro, open this form hidden.

    Here is what I did

    Macro AUTOEXEC.BAT

    OpenForm (Hidden View) (new hidden form)
    Open Form (this is the actual form you currently open)
    Go to the CONDITIONS area of this line and say this
    IIf([Forms]![NewHiddenFormName]![BirthDayFieldName] Is Null,False,True)
    then on the 3rd line say
    CLOSE (and choose the hidden form).

    What this does is open a hidden form that pulls from the query. If the first record is null, then it says do not open the birthday form, if it has a birthday, it says oepn the birthday form. Then the 3rd line just clioses that hidden form so it is not using up your resources.

  8. #8
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    Originally posted by tsyscps
    Heres how I solved the problem. I have a similar query that does the same.

    I created a form that is based on that query witrh the birthdys awithin 10 days. Save it and then in your autoexec macro, open this form hidden.

    Here is what I did

    Macro AUTOEXEC.BAT

    OpenForm (Hidden View) (new hidden form)
    Open Form (this is the actual form you currently open)
    Go to the CONDITIONS area of this line and say this
    IIf([Forms]![NewHiddenFormName]![BirthDayFieldName] Is Null,False,True)
    then on the 3rd line say
    CLOSE (and choose the hidden form).

    What this does is open a hidden form that pulls from the query. If the first record is null, then it says do not open the birthday form, if it has a birthday, it says oepn the birthday form. Then the 3rd line just clioses that hidden form so it is not using up your resources.



    Thanks for your answer. What if I want the form that pops up with the birthday within 10 days to pop up when another form is opened (not when database is opened). Sorry for the misunderstanding.

  9. #9
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    Here is the code that I ended up using on the form "On Open Event".

    Private Sub Form_Open(Cancel As Integer)

    If DCount("[FirstName]", "qFBirthdays") > 0 Then
    DoCmd.OpenForm "fFBirthdays", acNormal
    End If

    End Sub


    This works beautifully. Thanks everyone for all of the help. Its amazing what a few lines of code can do. I can't wait to learn this stuff.

    Thanks again Hooks

Posting Permissions

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