Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Where recordset is null? How? (Access 97 SR2)

    I have a sql statement that is stored is a string (stringsql). A form uses this as follows:

    Forms![frmArmyEdListExport]![frmSearchResults].Form.RecordSource = stringsql
    DoCmd.GoToControl "[frmSearchResults]"

    However, where the string returns no records i'd like it to display "No records", or something. I tried the following but it did not work:

    Set rec = CurrentDb().OpenRecordset(stringsql)
    If IsNull(rec) Then
    MsgBox "No Records"
    Else

    Can anyone give me an idea as to what i've done wrong. Much appreciated in advance for anyone that can help me with this.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Where recordset is null? How? (Access 97 SR2)

    Hi Phil,
    Try:
    If rec.EOF and rec.BOF then
    msgbox "No records"
    Else...
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where recordset is null? How? (Access 97 SR2)

    Excellent, thanks Rory for the incredibly quick response! I was having a look at the help files for rec.nomatch but couldn't work out how to use it properly.

    Another quick question on a slightly different note, you being the expert and all:
    Do you know of any websites that are good for Access/VBA job hunting. My current position has become a very unfriendly place to work and I no longer enjoy it (the workplace not the work, Access is excellent!), and I'm looking for something new in the UK (Kent/London area), but all I seem to find is Oracle jobs (which I think is too expensive for me to buy and learn at home!). I'm sure any ideas would be very helpful.

    Thanks again for solving the problem.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Where recordset is null? How? (Access 97 SR2)

    Hi Phil,
    NoMatch is used to tell if a Find method has succeeded or not.

    <hr>you being the expert and all<hr>
    I think you have me confused with Charlotte! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> This is not what I do for a living (well it sort of is, but only as an incidental part of my IT functions - I'm primarily an underwriter) - I'm still learning it myself! So I'm afraid I don't really know of any job sites for that sort of thing - perhaps someone else can suggest some?
    As for Oracle, in some ways it's reasonably similar - it does use SQL after all, though with some differences that can completely throw you - but in others it's a whole different ball game. I do know that we pay a lot of money every time we need things changed in our Oracle database, so perhaps it's worth the investment of learning it?!
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where recordset is null? How? (Access 97 SR2)

    Thanks for replying again Rory,
    I will definately have to look into NoMatch when I have a bit more time.

    As for Oracle, if there was a test version or something that I could learn it on i'd give it a go. However, i'm sure I looked at a web page a couple of weeks ago that had Oracle listed at about

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Where recordset is null? How? (Access 97 SR2)

    Hi Phil,
    There is, I think a personal version of Oracle - I have a book at work called something like "SQL the complete reference" which comes with a CD which has various SQL database types on it - that might be worth looking for.
    Bear in mind though that support for Oracle 7 ends at the end of this year, so trying to learn on version 8 or 9 might be a good idea - if you can only get version 7 cheaply, I imagine that's still a pretty good starting point. You might also want to look at one of the books on PL/SQL as that would probably be a key part of any Oracle job.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where recordset is null? How? (Access 97 SR2)

    Thanks for the advice - I will have a look around ASAP.

Posting Permissions

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