[RESOLVED] Access & SQL problems, again.

[I_Love_My_Vans]

Hello again, i have a table in an Access database holding entries from a diary. Each entry has a Subject, the Entry itself and the Username (The author of the entry).

I want to do a search of the entire entry table, for one specific user, for example the user would be "Jim", and in VB i would use the .RecordCount to count the number of records in that query, thus showing how many entries the user has made.

This current code will only show the user "Ben" to have 1 entry, where he really has 3? Any ideas.?

Sqltxt = "SELECT Entry.Username, Entry.Date, Entry.Subject, Entry.Entry"
Sqltxt = sqltxt = " From Entry WHERE"
Sqltxt = Sqltxt = " Entry.Username='" & UniUsername & "';"
Set MySearch = MyDatabase.OpenRecordset(Sqltxt)
    
lblEntry.Caption = "You have " & MySearch.RecordCount & " entries"

Thank you
ILMV

[lintz]

Try this...

Sqltxt = "SELECT * From Entry WHERE Username = '" & UniUsername & "'"
Set MySearch = MyDatabase.OpenRecordset(Sqltxt)
    
lblEntry.Caption = "You have " & MySearch.RecordCount & " entries"

[Joacim Andersson]

The RecordCount might not work in all cases it all depends on which type of cursor you're using. Sometimes you also need to move to the last position in the recordset before this property shows the correct number of records. A better approach might be to use the COUNT SQL statement.

Sqltxt = "SELECT COUNT(Entry.UserName) AS EntryCount WHERE Entry.UserName = '" & UniUsername & "'"
Set MySearch = MyDatabase.OpenRecordset(Sqltxt)
lblEntry.Caption = "You have " & MySearch("EntryCount") & " entries"

If you don't want to use that try to MoveLast and then MoveFirst in the recordset before you read the RecordCount.

[I_Love_My_Vans]

nope im affraid that hasnt worked.

Anything else :S

[Hack]

Have you verfied that Ben, in fact, has three entries?

I'm not seeing why, using DAO as you appear to be, that your code won't work.

[Joacim Andersson]

nope im affraid that hasnt worked.

Anything else :S

Was this referring to my reply or ....?

[I_Love_My_Vans]

YEs Ben does have three entries, as you can see form the attached image.

When i run the query like this...

sqltxt = "SELECT * From Entry"
Set mysearch = myusers.OpenRecordset(Sqltxt)

It returns 1 entry, which is incorrect as there are 4

When i do it like this (No query, straight from the table)

Set mysearch = myusers.OpenRecordset("Entry")

It returns 4 entries, which is correct?

[I_Love_My_Vans]

sorry Joacim

didnt see your post :S, will look at that now

[I_Love_My_Vans]

Thanks Joacim!

Didnt use you code, but gave me the idea to .MoveLast, and that has now worked.

Thank you all