Results 1 to 4 of 4

Thread: Forms & queries

  1. #1
    Join Date
    Oct 2004
    Location
    Edinburgh, Scotland
    Posts
    17

    Unanswered: Forms & queries

    I have a form where users can enter a phone number and click a button to check if that number is on any records in the database.
    When the user clicks the button I want a table to open showing all records containing that number.

    To put it simply I want to open and run a query through a form without seeing the design side of the query.

    Any ideas?

  2. #2
    Join Date
    Oct 2004
    Location
    Edinburgh, Scotland
    Posts
    17
    It's ok I worked it out for myself.

    Used:
    DoCmd.OpenQuery "NumberQry", , acReadOnly

    NumberQry being:
    SELECT [Vehicles for sale].[AdID], [Vehicles for sale].[Make], [Vehicles for sale].[Model], [Vehicles for sale].[Colour], [Vehicles for sale].[Year], [Vehicles for sale].[Engine Size], [Vehicles for sale].[Phone No], [Vehicles for sale].[Date], [Vehicles for sale].[Newspaper]
    FROM [Vehicles for sale]
    WHERE ((([Vehicles for sale].[Phone No])=[Forms]![NumberQry].[TxtPhoneNo]));

  3. #3
    Join Date
    Oct 2004
    Location
    Edinburgh, Scotland
    Posts
    17
    If the query finds no records containing that number I want a message box to appear saying "no matching records found".

    Can anyone give me an If Statement that will do this and not open an empty table?

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    You can do it using a RecordSet
    PHP Code:
      Sub ff
      Dim rs 
    as RecordSet
      Dim t 
    as String

       t
    "SELECT [Vehicles for sale].[AdID], [Vehicles for sale].[Make], [Vehicles for sale].[Model], [Vehicles for sale].[Colour], [Vehicles for sale].[Year], [Vehicles for sale].[Engine Size], [Vehicles for sale].[Phone No], [Vehicles for sale].[Date], [Vehicles for sale].[Newspaper]
    FROM [Vehicles for sale]
    WHERE ((([Vehicles for sale].[Phone No])=[Forms]![NumberQry].[TxtPhoneNo]));
    "
      
    Set rsCurrentDb.OpenRecordset(tdbOpenDynaset)
       If 
    rs.EOF and rs.BOF then
          MsgBox 
    ("Table is empty")
           
    Set rs Nothing
            
    Exit Sub
        
    ELSE
          
    rs.MoveLast
        END 
    IF
    End Sub 

Posting Permissions

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