Retrieve a record count in VBA

[crmc07]

I'm writing a database in Access and I need help with syntax for VB code. What i need it to do is after a name is chosen in the first field it triggers an event procedure that runs the following code:

Private Sub name_AfterUpdate()
iCount = "SELECT SUM(mainTable.roommate) FROM mainTable WHERE mainTable.name = Me!name.Value"
If (iCount = 0) Then
Me!roommate.RowSource = "SELECT employee.name " & _
                                     "FROM employee " & _
                                     "WHERE employee.name <> '" & Me!name.Value & "' AND employee.sex = (SELECT employee.sex FROM employee WHERE employee.name = '" & Me!name.Value & "') AND employee.name NOT IN (SELECT mainTable.roommate FROM mainTable) AND employee.name NOT IN (SELECT mainTable.name FROM mainTable) " & _
                                     "ORDER BY employee.name;"
Else
Me!roommate.RowSource = "SELECT mainTable.roommate " & _
                                     "FROM MainTable " & _
                                     "WHERE mainTable.name = '" & Me!name.Value & "';"
End If
End Sub

Once you chose the name it populates a list of names from another table based on the above. What i need it to do is check to see if the person chosen already has a roommate. If they don't then the first rowsource takes place. If they do then the second takes place. Currently when i chose a name nothing appears in the second box. I know the WHERE code in the if statement should work becuase i wrote that piece first and tested it before i even tried the if statement. Could someone help me and tell me what I'm doing wrong and maybe how i would fix it. VB is not something i program in frequently so my knowledge of it isn't the greatest.

Thanks

[Killer42]

I haven't read past the second line yet, because it's obviously wrong. iCount must be an Integer, but you are assigning a string to it. The fact that the string appears to contain an SQL statement is entirely irrelevant.

[crmc07]

I know it's wrong, the problem I'm having is how do i set that particular SQL statment to produce an integer instead of a string?

Also is there a way i could just write a SQL command into the if statement instead of assigning it to a variable first? I tried multiple ways of doing it but i keep getting syntax error each time.

[Killer42]

I know it's wrong, the problem I'm having is how do i set that particular SQL statment to produce an integer instead of a string?

You need to actually execute a command against the database. Just at the moment, I don't recall how to do so from within VBA. I'm going to move this discussion thread over to the Access forum, where you'll undoubtedly find a much better response.

Also is there a way i could just write a SQL command into the if statement instead of assigning it to a variable first? I tried multiple ways of doing it but i keep getting syntax error each time.

Off-hand, the only way I can think of would be to create a function do do the database command and return the record count, and use that function in your IF test.

Oh, and I'm going to change the title of the thread, too. "VB Help" is much too vague, nobody will know what it's about.

[jaxjagfan]

Look at using DCount for counts and DSums for sums. Both will accept viable WHERE clauses without the "WHERE".

If you are trying to do so with SQL then you are going to have to open a recordset with code to retrieve a count or sum.

[crmc07]

I haven't read past the second line yet, because it's obviously wrong. iCount must be an Integer, but you are assigning a string to it. The fact that the string appears to contain an SQL statement is entirely irrelevant.

I got why it is wrong. I was using the SUM function when i wanted to use the count function. It's been a long time since i used SQL.

I tried it with the Count instead of sum and kept gettign a blank box. Next I tried the Dcount with the following syntax:

Private Sub name_AfterUpdate()
iCount = DCount("mainTable.roommate", "[mainTable.roommate] = '*'")
If (iCount = 0) Then
Me!roommate.RowSource = "SELECT employee.name " & _
                        "FROM employee " & _
                        "WHERE employee.name <> '" & Me!name.Value & "' AND employee.sex = (SELECT employee.sex FROM employee WHERE employee.name = '" & Me!name.Value & "') AND employee.name NOT IN (SELECT mainTable.roommate FROM mainTable) AND employee.name NOT IN (SELECT mainTable.name FROM mainTable) " & _
                        "ORDER BY employee.name;"
Else
Me!roommate.RowSource = "SELECT mainTable.roommate " & _
                        "FROM MainTable " & _
                        "WHERE mainTable.name = '" & Me!name.Value & "';"
End If
End Sub
 

I still get nothing. Any other ideas what i may be doing wrong? I just can't seem to figure out the syntax to get it to do what i want.

[jaxjagfan]

iCount = DCount("[roommate]", "mainTable")

No criteria required if counting all records.

[crmc07]

iCount = DCount("[roommate]", "mainTable")

No criteria required if counting all records.

I tried that and still got a blank box. I then entered the following code:

iCount = DCount("[roommate]", "mainTable", "[roommate] = '" & Me!name.Value & "'")
 

I need this to count the number of records in the database where the name is filled in in the name box on the form. It should either be 1 or 0. I still got nothing. This is driving me a little crazy as i'm not sure why this will not work.

[Killer42]

What value is in name? Or to put it another way, can you print and copy the resulting string so we can see it?

Also, if it's supposed to be matching on wildcards, I believe you need to use the Like operator rather than =.

[crmc07]

What value is in name? Or to put it another way, can you print and copy the resulting string so we can see it?

Also, if it's supposed to be matching on wildcards, I believe you need to use the Like operator rather than =.

The value in "NAME" will be a list of names from another table. The user will select their name from a combo box in the form (I.E. EINSTEIN, ALBERT). The if statement is to then look in the main table and see if they were chosen as a roommate by another person. If they weren't then they will choose people from a list of names that is in the first Me!name.Rowsource above. If they were chosen then the only name they can choose from is then person that chose them.

The code would look something like this

iCount = DCount("[roommate]", "mainTable", "[roommate] = '" & Me!name.Value & "'")
 
* With the string
 
iCount = DCount("[roommate]", "mainTable", "[roommate] = EINSTEIN, ALBERT")
 

[jaxjagfan]

The value in "NAME" will be a list of names from another table. The user will select their name from a combo box in the form (I.E. EINSTEIN, ALBERT). The if statement is to then look in the main table and see if they were chosen as a roommate by another person. If they weren't then they will choose people from a list of names that is in the first Me!name.Rowsource above. If they were chosen then the only name they can choose from is then person that chose them.

The code would look something like this

iCount = DCount("[roommate]", "mainTable", "[roommate] = '" & Me!name.Value & "'")
 
* With the string
 
iCount = DCount("[roommate]", "mainTable", "[roommate] = EINSTEIN, ALBERT")
 

Just some thoughts - may not be viable in this time in your project but:

First - you are using "name" as the linking value. Not a good idea if you have 2 John Doe's. You should have an EmpID that uniquely identifies each employee.

Are COED selections allowed?

Are these employees being assigned to 2 person rooms or teams?

Make a room table - tblRooms (or tblTeams - whichever is politically correct) and make tblAssignedRooms. I'm going to use Rooms since you have "RoomMates" in your post. tblRooms would contain a list of all possible rooms and beds. If you have 200 rooms and 2 beds possible per room - tblRooms would contain list of all possible room/bed combinations.

Room_Bed
100-A
100-B
101-A
101-B ...

tblAssignedRooms would have entries something like:

Room_Bed, EmpID
100-A, 36
100-B, 24
101-A, 45
101-B, Null (vacant spot)

This would return a list of unassigned employees (not assigned a room):
"Select employees.EmpID, employees.Name, employees.sex from employees
Left Outer Join tblAssignedRooms on tblAssignedRooms.EmplD
Where tblAssignedRooms.EmplD is null"

Ex- if only 4 EmpID's 36, 24, 45, 48 it would show only EmpID 48 with its details

This would return a list of available rooms (all beds not taken):
"Select Left(tblRooms.Room_Bed,3) as Room, tblRooms.Room_Bed from tblRooms
Left Outer Join tblAssignedRooms on tblAssignedRooms.Room_Bed
Where tblAssignedRooms.Room_Bed is null
Order By tblAssignedRooms.Room_Bed"

Ex- if only 2 rooms 100, 101 it would show only 101-B

This following will show the details of the employee already assigned to the selected room (in our case the one assigned to 101-A) or will be null if no one assigned to the room yet.

"Select Left(tblRooms.Room_Bed, 3) AS Room, employees.EmpID, employees.Name, employees.sex from tblAssignedRooms, employees
Where tblAssignedRooms.EmpID = employees.EmpID
AND Left(tblRooms.Room_Bed,3) ='" & Me.lstRoom & "'"

Once a viable roommate is found "Accept Room" cmd button would run an update query.

Docmd.RunSQL "Insert Into tblAssignedRooms (Room_Bed, EmpID)
Values (" & varRmBed & ", " & varEmpID & ");"

Set the var variables when a room or employee is selected.

Maybe this will give you some ideas - maybe not. :O

[crmc07]

Just some thoughts - may not be viable in this time in your project but:

First - you are using "name" as the linking value. Not a good idea if you have 2 John Doe's. You should have an EmpID that uniquely identifies each employee.

Are COED selections allowed?

Are these employees being assigned to 2 person rooms or teams?

Make a room table - tblRooms (or tblTeams - whichever is politically correct) and make tblAssignedRooms. I'm going to use Rooms since you have "RoomMates" in your post. tblRooms would contain a list of all possible rooms and beds. If you have 200 rooms and 2 beds possible per room - tblRooms would contain list of all possible room/bed combinations.

Room_Bed
100-A
100-B
101-A
101-B ...

tblAssignedRooms would have entries something like:

Room_Bed, EmpID
100-A, 36
100-B, 24
101-A, 45
101-B, Null (vacant spot)

This would return a list of unassigned employees (not assigned a room):
"Select employees.EmpID, employees.Name, employees.sex from employees
Left Outer Join tblAssignedRooms on tblAssignedRooms.EmplD
Where tblAssignedRooms.EmplD is null"

Ex- if only 4 EmpID's 36, 24, 45, 48 it would show only EmpID 48 with its details

This would return a list of available rooms (all beds not taken):
"Select Left(tblRooms.Room_Bed,3) as Room, tblRooms.Room_Bed from tblRooms
Left Outer Join tblAssignedRooms on tblAssignedRooms.Room_Bed
Where tblAssignedRooms.Room_Bed is null
Order By tblAssignedRooms.Room_Bed"

Ex- if only 2 rooms 100, 101 it would show only 101-B

This following will show the details of the employee already assigned to the selected room (in our case the one assigned to 101-A) or will be null if no one assigned to the room yet.

"Select Left(tblRooms.Room_Bed, 3) AS Room, employees.EmpID, employees.Name, employees.sex from tblAssignedRooms, employees
Where tblAssignedRooms.EmpID = employees.EmpID
AND Left(tblRooms.Room_Bed,3) ='" & Me.lstRoom & "'"

Once a viable roommate is found "Accept Room" cmd button would run an update query.

Docmd.RunSQL "Insert Into tblAssignedRooms (Room_Bed, EmpID)
Values (" & varRmBed & ", " & varEmpID & ");"

Set the var variables when a room or employee is selected.

Maybe this will give you some ideas - maybe not. :O

This database is going to be used for one thing, for a person to choose their roommate and dinner at a conference my company is going to. The person will choose a roommate from a list. The list will consist of names who do not match the person choosing, of the same sex, and are not already chosen. I have the code created and tested it out before i tried to write the if statement and it works great. I just need to get the if statement to work so that if the person who put their name in was already chosen as a roommate by someone else then they will only have one name to chose from. otherwise they will have a list of same sex people who have yet to be chosen.

[crmc07]

This database is going to be used for one thing, for a person to choose their roommate and dinner at a conference my company is going to. The person will choose a roommate from a list. The list will consist of names who do not match the person choosing, of the same sex, and are not already chosen. I have the code created and tested it out before i tried to write the if statement and it works great. I just need to get the if statement to work so that if the person who put their name in was already chosen as a roommate by someone else then they will only have one name to chose from. otherwise they will have a list of same sex people who have yet to be chosen.

Anybody else have any ideas?

[crmc07]

OK I fine tuned the code and when i choose a name the update script is not running. When the name of the person is chosen all that comes up in the roommate section is nothing. Below is my code. Anyone know if it is the syntax of the code that is wrong or is it just not going to do what i need it to do

Private Sub name_AfterUpdate()
Dim iTotal As Long
Dim personName As String
personName = Me!name.Value
iTotal = DCount("[roommate]", "mainTable", "[name] LIKE '" & personName & "'")
If (iTotal = 0) Then
Me!roommate.RowSource = "SELECT employee.name " & _
                        "FROM employee " & _
                        "WHERE employee.name <> '" & Me!name.Value & "' AND employee.sex = (SELECT employee.sex FROM employee WHERE employee.name = '" & Me!name.Value & "') AND employee.name NOT IN (SELECT mainTable.roommate FROM mainTable) AND employee.name NOT IN (SELECT mainTable.name FROM mainTable) " & _
                        "ORDER BY employee.name;"
Else
Me!roommate.RowSource = "SELECT mainTable.roommate " & _
                        "FROM MainTable " & _
                        "WHERE mainTable.name = '" & Me!name.Value & "';"
End If
End Sub
 

[Killer42]

I think the first step would be to print out (and copy/paste to here) the exact string which is being passed to the SQL interpreter.

[Killer42]

I'm just starting to read the SQL, and I'm uncertain about something. Keeping in mind I'm somehat rusty on this and was no SQL expter to begin with. But I thought the following looked wrong...

AND employee.sex = (SELECT employee.sex FROM

I thought this sort of things was done with the IN operator. In other words, something like:

AND employee.sex IN (SELECT employee.sex FROM

[crmc07]

I'm just starting to read the SQL, and I'm uncertain about something. Keeping in mind I'm somehat rusty on this and was no SQL expter to begin with. But I thought the following looked wrong...

AND employee.sex = (SELECT employee.sex FROM

I thought this sort of things was done with the IN operator. In other words, something like:

AND employee.sex IN (SELECT employee.sex FROM

That's where part of my problem was. I just noticed and fixed it about an hour ago. Now the new problem I'm facing is that the iCount is being passed a null which is interpreted as 0 so the ELSE statement will never run.

Dim iTotal As Integer
iTotal = DCount("[roommate]", "mainTable", "[mainTable.name] = '" & Me!name.Value & "'")
MsgBox (iCount)
 

I printed out a msgbox to see what was passed and it is blank. Any idea on how to fix this?

[crmc07]

I just wanted to thank everyone for thier help. I FINALLY got it to work (for now). It does exactly what i need it to do...Thanks again!!!

[Killer42]

I just wanted to thank everyone for thier help. I FINALLY got it to work (for now). It does exactly what i need it to do...Thanks again!!!

Glad to see you've got it sorted out.

One question, though. I notice that you appear to have made up variable name iCount in the MsgBox statement, where it should have been iTotal. Presumably you've now fixed this. But it highlights a bigger potential problem. You should configure VB set to require explicit variable declaration. That way, when you mistype a variable name, or make one up on the fly like this, VB will spot the error and refuse to compile until you fix it. Do you have it configured this way?

[crmc07]

Glad to see you've got it sorted out.

One question, though. I notice that you appear to have made up variable name iCount in the MsgBox statement, where it should have been iTotal. Presumably you've now fixed this. But it highlights a bigger potential problem. You should configure VB set to require explicit variable declaration. That way, when you mistype a variable name, or make one up on the fly like this, VB will spot the error and refuse to compile until you fix it. Do you have it configured this way?

Yeah i did figure that out. After looking at code for so long it all tends to run together after a while.