Incomplete record makes Record Count = 0? 2

[KerryL]

I have some basic code that performs a task as long as the Record Count > 0. However, if the record it is using does not contain a valid number in one of the fields then for some reason the Record Count = 0.

Below is the section of code in question. Specifically, if the SupvEmail is null or 0,the Record Count = 0 and then the code skips down to the ELSE MsgBox.


/CODE
With rstEmail
If .RecordCount > 0 Then
If GenlInfo.Contact = "" Then
Call GetGenlInfo
GenlInfo = ggiGenlInfo
End If

Do While Not .EOF
' Check for missing data
' Verify email address exists in the table
If Nz(.Fields("StaffEmail&quot) = "" Then
MsgBox "Cardholder '" & .Fields("StaffFName&quot & " " & .Fields("StaffLName&quot & _
"' does not have an email address in the table." & vbCrLf & _
"Use Staff Maintenance form to add the cardholder's email address" & _
" and then try again to email the findings.", vbInformation = vbOKOnly, _
"Missing Email Address"
Exit Sub
Else
strTo = .Fields("StaffEmail&quot

' Supervisor email address
If Nz(.Fields("SpvsrEmail&quot) = "" Then
strCC = GenlInfo.ContactEmail
Else
strCC = .Fields("SpvsrEmail&quot
End If

... <snip emailing code for brevity>

End If 'missing email address
.MoveNext
Loop

MsgBox &quot;Emails have been sent to cardholders&quot;, , &quot;Email Sent&quot;
Else
MsgBox &quot;There were no staff details for this document that have not been sent.&quot;, _
vbOKOnly + vbInformation, &quot;No Data&quot;
End If 'detail records exist
End With

/CODE


Although the SupvEmail doesn't contain a number, there IS a record there, so I don't understand why Record Count = 0.

TIA for any ideas or help.

KerryL

 

[BillPower]

Hi KerryL,

Could you post the SQL that creates the Recordset, sounds like the problem must be in that, not in the code above.

Should SupvEmail be SpvsrEmail or vice versa.

Regards

Bill

 

[KerryL]

Thanks for your offer to try and help, Bill.
Here's the code that creates the recordset:

'Send email notifications to CardHolders
Set dbs = CurrentDb
Set qryEmail = dbs.QueryDefs(&quot;qryEmailInfo&quot
qryEmail.Parameters(&quot;pHeaderID&quot = CLng(txtHeaderID)
Set rstEmail = qryEmail.OpenRecordset

'Set focus on main form to first detail
Me.CycleNo.SetFocus
SendKeys &quot;+{Tab 4}&quot;, True

'If txtEmailtoCardHolders is null, email is sent to each cardholder
If Not txtEmailToCardHolders Then
MsgBox &quot;Staff details for this document have &quot; & _
&quot;already been sent.&quot;, vbOKOnly + vbInformation, &quot;Emails Already Sent&quot;
Exit Sub
Else
End If


With rstEmail
If .RecordCount > 0 Then
If GenlInfo.Contact = &quot;&quot; Then
Call GetGenlInfo
GenlInfo = ggiGenlInfo
End If

Do While Not .EOF
<ETC>


Regarding SpvsrEmail: All cardholders have a StaffID; some cardholders are supervisors; each cardholder has a supervisor number assigned to them which corresponds to the StaffID of their supervisor. Within the query, SpvsrEmail is created by linking the cardholder's Supervisor Number to the corresponding StaffID number. (Hope that makes sense.)

 

[BillPower]

Hi KerryL , still can't help without seeing the SQL for &quot;qryEmailInfo&quot;.

Would it be possible to send me a copy of your DB. Put some dummy records in your Tables if necessary. My Email is billpower@cwcom.net . If the problem is an obvious one I'll get back to you immediately in this Thread, if not it might be a day or two. I'm in the middle of trying to sort out another Thread.

Initially, I thought I'd ask for your code and SQL and just quickly try to reproduce your problem here. In retrospect, I think this might be a bit too difficult to do without being able to view your Table(s) and Query(ies).

Anyway, if it's not convenient for you to send a copy DB, still post the SQL for the Query above.

Regards

Bill

 

[JeremyNYC]

Please post the sql here, so that we can all learn from this process. That's what the site is for, and that's the best use of it.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[BillPower]

JeremyNYC,

I often request users to send their DB's to me, usually when I think it will save both the member and myself time in coming to a possible solution to a problem. On receipt of the DB every bit of correspondense is posted in the Thread, for every Member to view and possibly learn from and to add to themselves if they want. I often post examples of these DB’s at

http://www.wpower.fsbusiness.co.uk/

On average 2 members a week send their DB’s to me on request. The last example of this was yesterday in thread705-551198 . If you care to take a look at the Thread I’m sure this will backup the fact that it is sometimes more expedient for someone to offer to look at a DB. Four Members participated in thread705-551198 and we all ended up happy and to top it all, the person who asked the question got Emailed a working example of the solution. Not blowing my own trumpet, I’ve now sent approximately 20 Members working examples of the solutions to their questions and posted everything concerning the solutions in the respective Threads. What better advertisement for Tek-Tips.

My first reply did ask for KerryL to post the SQL for the RecordSet. My second explained that if KerryL wanted to send their DB it would save the difficulty of trying to reproduce their Tables/Queries. In my opinion, without someone setting sight on the Table(s), this is going to be a very difficult question to solve.

Regards

Bill

 

[KerryL]

I tend to agree that it will be much more difficult to solve the problem without being able to look at the query the recordset is based on, and also how the tables are built and linked.

I have code to deal with the fact that a card holder's record doesn't have a supervisor assigned, but when it gets to that record the code is never used because for some reason the Record Count goes to zero, which signifies that there's nothing more to process and the sub is exited.

FYI, I'm testing a way around this by adding a record named &quot;No Supervisor&quot; and using this record for the card holders that don't have an official supervisor. (This typically occurs for people who are in the tables as supervisors only. Which makes me wonder if I should've created a separate table for supervisors.)

Bill, I'll zip the DB and email it to you this morning.

 

[KerryL]

Jeremy/Bill,

FYI, here is the SQL from qryEmailInfo:


PARAMETERS pHeaderID Long;
SELECT tblDetails.HeaderID, tblDetails.DetailID, tblDetails.Amount, tblDetails.Findings, tblDetails.FYI_RFI, tblStaff.StaffLName, tblStaff.StaffFName, tblStaff.StaffEmail, tblStaff_1.StaffEmail AS SpvsrEmail, tblHeader.EmailToCardHolders
FROM tblHeader INNER JOIN ((tblDetails INNER JOIN tblStaff ON tblDetails.StaffID = tblStaff.StaffID) INNER JOIN tblStaff AS tblStaff_1 ON tblStaff.SupvNumber = tblStaff_1.StaffID) ON tblHeader.HeaderID = tblDetails.HeaderID
WHERE (((tblDetails.HeaderID)=[pHeaderID]))
WITH OWNERACCESS OPTION;

 

[BillPower]

Hi KerryL,

Thanks for sending your DB. In your Query qryEmailInfo I've just had to change the Join Type from INNER to LEFT for tblStaff to tblStaff_1, i.e. Include ALL Records from tblStaff and only those Records from tblStaff_1 where the JOINED Fields are EQUAL.

Saved a lot of hassle being able to see the Query in Design View.

PARAMETERS pHeaderID Long;
SELECT tblDetails.HeaderID, tblDetails.DetailID, tblDetails.Amount, tblDetails.Findings, tblDetails.FYI_RFI, tblStaff.StaffLName, tblStaff.StaffFName, tblStaff.StaffEmail, tblStaff_1.StaffEmail AS SpvsrEmail, tblHeader.EmailToCardHolders
FROM tblHeader INNER JOIN ((tblDetails INNER JOIN tblStaff ON tblDetails.StaffID = tblStaff.StaffID) LEFT JOIN tblStaff AS tblStaff_1 ON tblStaff.SupvNumber = tblStaff_1.StaffID) ON tblHeader.HeaderID = tblDetails.HeaderID
WHERE (((tblDetails.HeaderID)=[pHeaderID]))
WITH OWNERACCESS OPTION;

Let me know how you get on. Working example (I think) in the Email.

Good Luck

Bill

 

[KerryL]

Bill,
I modified the query SQL as you suggested and that portion of the code seems to be working fine now. Thank you. (I'll let you know if I find more problems in my data tests.)

But can you explain why the LEFT join works but the INNER join did not? I don't understand the difference well enough to know why the change fixed the problem.

Thanks,
Kerry

 

[BillPower]

I'm just going to try to explain this using TableA and TableB both with a Field1 as an example, the Tables have these Records:

TableA TableB
1 1
2 2
3 4
4 5
6

Using a LEFT JOIN would return All Table1 Records with a Record in Field1 in Table1 whether Field1 in Table2 has a Record or Not and Records from Table2 where Field1 Equals Field1 in Table1:

TableA TableB
1 1
2 2
3
4 4
6

Using a RIGHT JOIN would return All Table2 Records with a Record in Field1 in Table2 whether Field1 in Table1 has a Record or Not and Records from Table1 where Field1 Equals Field1 in Table2:

TableA TableB
1 1
2 2
4 4
5

Using an INNER JOIN would return only Records where Both Tables have Records that are Equal in Field1:

TableA TableB
1 1
2 2
4 4


Your Query didn't always have a Record in tblStaff_1 so didn't always Return a Record using an INNER JOIN. tblStaff always has a Record so will always Return a Record if the Parameter is met Hence the LEFT JOIN.

Bit difficult to explain.

Regards

Bill

 

[KerryL]

Wow, I had to read through your example a couple times before I saw the pattern. And I was still not sure I understood it until you mentioned why my query wasn't working correctly, then it made sense. I doubt if I would've figured out the problem on my own.

Again, thanks very much for your help Bill.

 

[LikeThisName]

thankyou for sharing Bill!