VB string comparsion

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

Hi everyone,

I'm trying to do a string comparsion with the retrieve
records using DAO and I'm stuck.

Here is the code:

Dim db As DAO.Database
Dim user As DAO.Recordset
Dim strstring As String
dim tnum AS integer

user = " Mike"
tnum = DCount("UserName", "User")

strstring = "SELECT UserName FROM User"

Set db = CurrentDb()
Set rstuser = db.OpenRecordset("User")

If Not rstuser.BOF And Not rstuser.EOF Then

** At this point, I'm stuck. What do I need to do so that
I can compare the user name(mention above) to the retrieve
records. If the user name does not match, it display a
message box and exit.

End If

Any help is appreciate,

Ben
 
I think you want something like this (untested):

Public Function DoSomethingWithUser(strUser as String)

Dim strSQL as String
Dim db as DAO.Database
Dim rst as DAO.Recordset

strSQL = "SELECT * FROM tblUser WHERE UserName = '" & strUser & "'"

set db = CurrentDB()
set rst = db.OpenRecordset(strSQL)

With rst
If .RecordCount Then
.MoveFirst
Do Until .Eof
Debug.Print !NameOfField
'Do what you need with the record here
.MoveNext
Loop
Else
MsgBox "There are no matching records"
End If
.Close
End With

set rst = Nothing
db.Close
set db = Nothing

End Function

This function takes a string argument so to get it to work on the user
"Mike" you would call the function by typing

DoSomethingWithUser "Mike".

The code will open a recordset that contains all fields in a table called
tblUser where UserName = 'Mike'. If no records are returned it will display
a message saying "there are no matching records". If there are records then
it will print the value of the field called "NameOfField" into the immediate
window. (Obviously you don't have a field called this, and will want to do
something a bit more useful with the results!) I hope this is the sort of
thing that you wanted.

Note that I have used a naming convention in this code to make it easier to
follow. You have partly done this in your code, but you seem to have used
the word "user" in three different contexts, and that makes it rather hard
to understand what you are trying to do:

- first you have put "Dim user as DAO.recordset" - so you have defined user
as a recordset object
- then you have "user = " Mike"" - which means you are treating it like a
string variable, and this line will give a type mismatch error.
- then you have DCount("UserName","User") which imples you have a table
called User as well (as does the SQL on the next line)

strstring = "SELECT UserName FROM User" looks like at SQL statement, but you
have not used a WHERE clause so would get all records in the table. Also,
you have not used it for anything - I would have thought that this would be
what you would need to use for the recordset.

Set rstuser = db.OpenRecordset("User") - this uses an undefined variable
name (rstuser), so this line should fail if have turned on Option Explicit.
Even if it did work it would open a recordset based on a table called "User"
and would return all fields and records. This would not depend on any of the
preceeding code, so I doubt it's what you wanted to do!
 
Thanks,
Ben
-----Original Message-----
I think you want something like this (untested):

Public Function DoSomethingWithUser(strUser as String)

Dim strSQL as String
Dim db as DAO.Database
Dim rst as DAO.Recordset

strSQL = "SELECT * FROM tblUser WHERE UserName = '" & strUser & "'"

set db = CurrentDB()
set rst = db.OpenRecordset(strSQL)

With rst
If .RecordCount Then
.MoveFirst
Do Until .Eof
Debug.Print !NameOfField
'Do what you need with the record here
.MoveNext
Loop
Else
MsgBox "There are no matching records"
End If
.Close
End With

set rst = Nothing
db.Close
set db = Nothing

End Function

This function takes a string argument so to get it to work on the user
"Mike" you would call the function by typing

DoSomethingWithUser "Mike".

The code will open a recordset that contains all fields in a table called
tblUser where UserName = 'Mike'. If no records are returned it will display
a message saying "there are no matching records". If there are records then
it will print the value of the field called "NameOfField" into the immediate
window. (Obviously you don't have a field called this, and will want to do
something a bit more useful with the results!) I hope this is the sort of
thing that you wanted.

Note that I have used a naming convention in this code to make it easier to
follow. You have partly done this in your code, but you seem to have used
the word "user" in three different contexts, and that makes it rather hard
to understand what you are trying to do:

- first you have put "Dim user as DAO.recordset" - so you have defined user
as a recordset object
- then you have "user = " Mike"" - which means you are treating it like a
string variable, and this line will give a type mismatch error.
- then you have DCount("UserName","User") which imples you have a table
called User as well (as does the SQL on the next line)

strstring = "SELECT UserName FROM User" looks like at SQL statement, but you
have not used a WHERE clause so would get all records in the table. Also,
you have not used it for anything - I would have thought that this would be
what you would need to use for the recordset.

Set rstuser = db.OpenRecordset("User") - this uses an undefined variable
name (rstuser), so this line should fail if have turned on Option Explicit.
Even if it did work it would open a recordset based on a table called "User"
and would return all fields and records. This would not depend on any of the
preceeding code, so I doubt it's what you wanted to do!






.
Click to expand...
 
Back
Top