Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

SQL statment

Discussion in 'Information Technology' started by Tom, Jul 28, 2006.

Tags:
  1. Tom

    Tom
    Expand Collapse
    Guest

    I have a form based on a query.

    While the form loading I want to check if there`re any results.
    If no results, msgbox say "No Data found"

    I think that sql statment will do the job, but i find hard to wrap it up
    into a sub.
    "SELECT DISTINCTROW Chartqry.Price, Count(*) AS [Count Of Chartqry] FROM
    Chartqry GROUP BY Chartqry.Price;"


    Thanks,

    Tom
     
  2. Loading...

    Similar Threads Forum Date
    Microsoft Brings 64-Bit Perks in SQL Server 2000 Service Pack (Ziff Davis) Interfaith Dialogues May 7, 2005

  3. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    Use a recordset:

    Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Err_Form_Open

    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone

    If rst.EOF And rst.BOF Then
    MsgBox "There are no records to edit. This form will not open."
    Cancel = True
    End If

    Exit_Form_Open:
    Exit Sub

    Err_Form_Open:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Form_Open

    End Sub
    --
    Arvin Meyer, MCP, MVP
    Microsoft Access
    Free Access downloads
    http://www.datastrat.com
    http://www.mvps.org/access


    "Tom" <partner1973@yahoo.com> wrote in message
    news:O1JfVYtfGHA.3652@TK2MSFTNGP02.phx.gbl...
    > I have a form based on a query.
    >
    > While the form loading I want to check if there`re any results.
    > If no results, msgbox say "No Data found"
    >
    > I think that sql statment will do the job, but i find hard to wrap it up
    > into a sub.
    > "SELECT DISTINCTROW Chartqry.Price, Count(*) AS [Count Of Chartqry] FROM
    > Chartqry GROUP BY Chartqry.Price;"
    >
    >
    > Thanks,
    >
    > Tom
    >
    >
     
  4. Tom

    Tom
    Expand Collapse
    Guest

    Thanks Arvin,
    It`s look elegant, but when I tried I got error message:
    7951: you entered an expression that has invalid reference to the
    RecordsetClone property.
    What is that mean? how can I fix this?

    Thanks again,
    Tom

    "Arvin Meyer [MVP]" <a@m.com> wrote in message
    news:e7RuUktfGHA.2172@TK2MSFTNGP04.phx.gbl...
    > Use a recordset:
    >
    > Private Sub Form_Open(Cancel As Integer)
    > On Error GoTo Err_Form_Open
    >
    > Dim rst As DAO.Recordset
    > Set rst = Me.RecordsetClone
    >
    > If rst.EOF And rst.BOF Then
    > MsgBox "There are no records to edit. This form will not open."
    > Cancel = True
    > End If
    >
    > Exit_Form_Open:
    > Exit Sub
    >
    > Err_Form_Open:
    > MsgBox Err.Number & ": " & Err.Description
    > Resume Exit_Form_Open
    >
    > End Sub
    > --
    > Arvin Meyer, MCP, MVP
    > Microsoft Access
    > Free Access downloads
    > http://www.datastrat.com
    > http://www.mvps.org/access
    >
    >
    > "Tom" <partner1973@yahoo.com> wrote in message
    > news:O1JfVYtfGHA.3652@TK2MSFTNGP02.phx.gbl...
    >> I have a form based on a query.
    >>
    >> While the form loading I want to check if there`re any results.
    >> If no results, msgbox say "No Data found"
    >>
    >> I think that sql statment will do the job, but i find hard to wrap it up
    >> into a sub.
    >> "SELECT DISTINCTROW Chartqry.Price, Count(*) AS [Count Of Chartqry] FROM
    >> Chartqry GROUP BY Chartqry.Price;"
    >>
    >>
    >> Thanks,
    >>
    >> Tom
    >>
    >>

    >
    >
     
  5. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    Make sure that you have a reference set to DAO (3.6 for Access 2000 and
    greater). Do that in any code window:

    Tools ... References
    --
    Arvin Meyer, MCP, MVP
    Microsoft Access
    Free Access downloads
    http://www.datastrat.com
    http://www.mvps.org/access

    "Tom" <partner1973@yahoo.com> wrote in message
    news:OYOjmutfGHA.4976@TK2MSFTNGP02.phx.gbl...
    > Thanks Arvin,
    > It`s look elegant, but when I tried I got error message:
    > 7951: you entered an expression that has invalid reference to the
    > RecordsetClone property.
    > What is that mean? how can I fix this?
    >
    > Thanks again,
    > Tom
    >
    > "Arvin Meyer [MVP]" <a@m.com> wrote in message
    > news:e7RuUktfGHA.2172@TK2MSFTNGP04.phx.gbl...
    > > Use a recordset:
    > >
    > > Private Sub Form_Open(Cancel As Integer)
    > > On Error GoTo Err_Form_Open
    > >
    > > Dim rst As DAO.Recordset
    > > Set rst = Me.RecordsetClone
    > >
    > > If rst.EOF And rst.BOF Then
    > > MsgBox "There are no records to edit. This form will not open."
    > > Cancel = True
    > > End If
    > >
    > > Exit_Form_Open:
    > > Exit Sub
    > >
    > > Err_Form_Open:
    > > MsgBox Err.Number & ": " & Err.Description
    > > Resume Exit_Form_Open
    > >
    > > End Sub
    > > --
    > > Arvin Meyer, MCP, MVP
    > > Microsoft Access
    > > Free Access downloads
    > > http://www.datastrat.com
    > > http://www.mvps.org/access
    > >
    > >
    > > "Tom" <partner1973@yahoo.com> wrote in message
    > > news:O1JfVYtfGHA.3652@TK2MSFTNGP02.phx.gbl...
    > >> I have a form based on a query.
    > >>
    > >> While the form loading I want to check if there`re any results.
    > >> If no results, msgbox say "No Data found"
    > >>
    > >> I think that sql statment will do the job, but i find hard to wrap it

    up
    > >> into a sub.
    > >> "SELECT DISTINCTROW Chartqry.Price, Count(*) AS [Count Of Chartqry]

    FROM
    > >> Chartqry GROUP BY Chartqry.Price;"
    > >>
    > >>
    > >> Thanks,
    > >>
    > >> Tom
    > >>
    > >>

    > >
    > >

    >
    >
     
  6. Tom

    Tom
    Expand Collapse
    Guest

    Thank you Arvin for your time.
    eventually, I used Dcount.

    Thanks,
    Tom
    "Arvin Meyer [MVP]" <a@m.com> wrote in message
    news:Od4eCgyfGHA.1320@TK2MSFTNGP04.phx.gbl...
    > Make sure that you have a reference set to DAO (3.6 for Access 2000 and
    > greater). Do that in any code window:
    >
    > Tools ... References
    > --
    > Arvin Meyer, MCP, MVP
    > Microsoft Access
    > Free Access downloads
    > http://www.datastrat.com
    > http://www.mvps.org/access
    >
    > "Tom" <partner1973@yahoo.com> wrote in message
    > news:OYOjmutfGHA.4976@TK2MSFTNGP02.phx.gbl...
    >> Thanks Arvin,
    >> It`s look elegant, but when I tried I got error message:
    >> 7951: you entered an expression that has invalid reference to the
    >> RecordsetClone property.
    >> What is that mean? how can I fix this?
    >>
    >> Thanks again,
    >> Tom
    >>
    >> "Arvin Meyer [MVP]" <a@m.com> wrote in message
    >> news:e7RuUktfGHA.2172@TK2MSFTNGP04.phx.gbl...
    >> > Use a recordset:
    >> >
    >> > Private Sub Form_Open(Cancel As Integer)
    >> > On Error GoTo Err_Form_Open
    >> >
    >> > Dim rst As DAO.Recordset
    >> > Set rst = Me.RecordsetClone
    >> >
    >> > If rst.EOF And rst.BOF Then
    >> > MsgBox "There are no records to edit. This form will not open."
    >> > Cancel = True
    >> > End If
    >> >
    >> > Exit_Form_Open:
    >> > Exit Sub
    >> >
    >> > Err_Form_Open:
    >> > MsgBox Err.Number & ": " & Err.Description
    >> > Resume Exit_Form_Open
    >> >
    >> > End Sub
    >> > --
    >> > Arvin Meyer, MCP, MVP
    >> > Microsoft Access
    >> > Free Access downloads
    >> > http://www.datastrat.com
    >> > http://www.mvps.org/access
    >> >
    >> >
    >> > "Tom" <partner1973@yahoo.com> wrote in message
    >> > news:O1JfVYtfGHA.3652@TK2MSFTNGP02.phx.gbl...
    >> >> I have a form based on a query.
    >> >>
    >> >> While the form loading I want to check if there`re any results.
    >> >> If no results, msgbox say "No Data found"
    >> >>
    >> >> I think that sql statment will do the job, but i find hard to wrap it

    > up
    >> >> into a sub.
    >> >> "SELECT DISTINCTROW Chartqry.Price, Count(*) AS [Count Of Chartqry]

    > FROM
    >> >> Chartqry GROUP BY Chartqry.Price;"
    >> >>
    >> >>
    >> >> Thanks,
    >> >>
    >> >> Tom
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     

Share This Page