How do you display the values of a recordset in a message?

J

Jack

Hi,
I have the following code that checks to see if the master table has
TBSGROUP as null value when compared to the TBSGROUP in the main table. If
there is null value in MASTER TABLE then I am trying not to allow the report
menu to open up. At the same time I am displaying message to update the
master table that has null value. However, I would like to tell the user in
the message box the value of the tbsgroup that need to be filled in the
master table. How does one display the value in the message box? Any help is
appreciated. Thanks.

CODE:
Dim TBS_db As Database
Dim TBS_rs As Recordset
Dim str_SQL As String
Set TBS_db = CurrentDb
str_SQL = "SELECT qryDistinctTBSGroup.[TBS Grp] AS NotInMaster FROM
qryDistinctTBSGroup LEFT JOIN dbo_tblLineItems ON qryDistinctTBSGroup.[TBS
Grp] = dbo_tblLineItems.LineItemID WHERE (((dbo_tblLineItems.LineItemID) Is
Null)) ORDER BY qryDistinctTBSGroup.[TBS Grp]"
Set TBS_rs = TBS_db.OpenRecordset(str_SQL)

If Not TBS_rs.EOF Then
Forms!frmMainMenu!Command32.Visible = True
MsgBox ("You cannot run reports unless TBS Master table is in sync with
tblTBS TBSGroup. Please check records to add")
Exit Sub
Else
DoCmd.OpenForm "frmReportsMenu", acNormal, , , , acWindowNormal
End If
 
A

Alex Dybenko

Hi,
try this:

Dim TBS_db As Database
Dim TBS_rs As Recordset
Dim str_SQL As String
Set TBS_db = CurrentDb

str_SQL = "SELECT qryDistinctTBSGroup.[TBS Grp] AS NotInMaster,
dbo_tblLineItems.LineItemID FROM
qryDistinctTBSGroup LEFT JOIN dbo_tblLineItems ON qryDistinctTBSGroup.[TBS
Grp] = dbo_tblLineItems.LineItemID ORDER BY qryDistinctTBSGroup.[TBS Grp]"
Set TBS_rs = TBS_db.OpenRecordset(str_SQL)

If Not TBS_rs.EOF Then
if isnull(TBS_rs!LineItemID) then
Forms!frmMainMenu!Command32.Visible = True
MsgBox ("You cannot run reports unless TBS Master table is in sync with
tblTBS TBSGroup, value: " & TBS_rs![TBS Grp] & ". Please check records to
add")
Exit Sub
Else
DoCmd.OpenForm "frmReportsMenu", acNormal, , , , acWindowNormal
End If

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
 
J

Jack

Thanks Alex for your help. I appreciate it. THe code change works fine when I
have one record out of sync. However, when more than one record is out of
sync, then only the first record is getting displayed in the message box. I
would like to display all the records in the messagebox. Is this possible by
change of code. Thanks again.

Alex Dybenko said:
Hi,
try this:

Dim TBS_db As Database
Dim TBS_rs As Recordset
Dim str_SQL As String
Set TBS_db = CurrentDb

str_SQL = "SELECT qryDistinctTBSGroup.[TBS Grp] AS NotInMaster,
dbo_tblLineItems.LineItemID FROM
qryDistinctTBSGroup LEFT JOIN dbo_tblLineItems ON qryDistinctTBSGroup.[TBS
Grp] = dbo_tblLineItems.LineItemID ORDER BY qryDistinctTBSGroup.[TBS Grp]"
Set TBS_rs = TBS_db.OpenRecordset(str_SQL)

If Not TBS_rs.EOF Then
if isnull(TBS_rs!LineItemID) then
Forms!frmMainMenu!Command32.Visible = True
MsgBox ("You cannot run reports unless TBS Master table is in sync with
tblTBS TBSGroup, value: " & TBS_rs![TBS Grp] & ". Please check records to
add")
Exit Sub
Else
DoCmd.OpenForm "frmReportsMenu", acNormal, , , , acWindowNormal
End If

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Jack said:
Hi,
I have the following code that checks to see if the master table has
TBSGROUP as null value when compared to the TBSGROUP in the main table. If
there is null value in MASTER TABLE then I am trying not to allow the
report
menu to open up. At the same time I am displaying message to update the
master table that has null value. However, I would like to tell the user
in
the message box the value of the tbsgroup that need to be filled in the
master table. How does one display the value in the message box? Any help
is
appreciated. Thanks.

CODE:
Dim TBS_db As Database
Dim TBS_rs As Recordset
Dim str_SQL As String
Set TBS_db = CurrentDb
str_SQL = "SELECT qryDistinctTBSGroup.[TBS Grp] AS NotInMaster FROM
qryDistinctTBSGroup LEFT JOIN dbo_tblLineItems ON qryDistinctTBSGroup.[TBS
Grp] = dbo_tblLineItems.LineItemID WHERE (((dbo_tblLineItems.LineItemID)
Is
Null)) ORDER BY qryDistinctTBSGroup.[TBS Grp]"
Set TBS_rs = TBS_db.OpenRecordset(str_SQL)

If Not TBS_rs.EOF Then
Forms!frmMainMenu!Command32.Visible = True
MsgBox ("You cannot run reports unless TBS Master table is in sync with
tblTBS TBSGroup. Please check records to add")
Exit Sub
Else
DoCmd.OpenForm "frmReportsMenu", acNormal, , , , acWindowNormal
End If
Click to expand...
Click to expand...
 
P

pietlinden

Thanks Alex for your help. I appreciate it. THe code change works fine when I
have one record out of sync. However, when more than one record is out of
sync, then only the first record is getting displayed in the message box. I
would like to display all the records in the messagebox. Is this possible by
change of code. Thanks again.



Alex Dybenko said:
Hi,
try this:
Click to expand...
Dim TBS_db As Database
Dim TBS_rs As Recordset
Dim str_SQL As String
Set TBS_db = CurrentDb
Click to expand...
str_SQL = "SELECT qryDistinctTBSGroup.[TBS Grp] AS NotInMaster,
dbo_tblLineItems.LineItemID FROM
qryDistinctTBSGroup LEFT JOIN dbo_tblLineItems ON qryDistinctTBSGroup.[TBS
Grp] = dbo_tblLineItems.LineItemID ORDER BY qryDistinctTBSGroup.[TBS Grp]"
Set TBS_rs = TBS_db.OpenRecordset(str_SQL)
Click to expand...
If Not TBS_rs.EOF Then
if isnull(TBS_rs!LineItemID) then
Forms!frmMainMenu!Command32.Visible = True
MsgBox ("You cannot run reports unless TBS Master table is in sync with
tblTBS TBSGroup, value: " & TBS_rs![TBS Grp] & ". Please check records to
add")
Exit Sub
Else
DoCmd.OpenForm "frmReportsMenu", acNormal, , , , acWindowNormal
End If
Click to expand...
--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
Click to expand...
Jack said:
Hi,
I have the following code that checks to see if the master table has
TBSGROUP as null value when compared to the TBSGROUP in the main table. If
there is null value in MASTER TABLE then I am trying not to allow the
report
menu to open up. At the same time I am displaying message to update the
master table that has null value. However, I would like to tell the user
in
the message box the value of the tbsgroup that need to be filled in the
master table. How does one display the value in the message box? Any help
is
appreciated. Thanks.
CODE:
Dim TBS_db As Database
Dim TBS_rs As Recordset
Dim str_SQL As String
Set TBS_db = CurrentDb
str_SQL = "SELECT qryDistinctTBSGroup.[TBS Grp] AS NotInMaster FROM
qryDistinctTBSGroup LEFT JOIN dbo_tblLineItems ON qryDistinctTBSGroup.[TBS
Grp] = dbo_tblLineItems.LineItemID WHERE (((dbo_tblLineItems.LineItemID)
Is
Null)) ORDER BY qryDistinctTBSGroup.[TBS Grp]"
Set TBS_rs = TBS_db.OpenRecordset(str_SQL)
If Not TBS_rs.EOF Then
Forms!frmMainMenu!Command32.Visible = True
MsgBox ("You cannot run reports unless TBS Master table is in sync with
tblTBS TBSGroup. Please check records to add")
Exit Sub
Else
DoCmd.OpenForm "frmReportsMenu", acNormal, , , , acWindowNormal
End If- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...

loop through the open recordset, append the fields you want to a
string variable, build your message in another string variable, and
then display it with msgbox.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Stored Procedure runs but displays #Name? in some of the output fi 1
Checking a value in a recordset 3
Incorrect count in filtered recordset 0
Why do I receive no value error message, when parameters do have v 2
How do you doubleclick on a recordset to open recordset in form vi 3
Msg Box with Conditions and display a value from form on message 4
how do I not display null values on report 7
How do you get the maximum value to display in text box form 8

Top