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

G

Guest

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
 
G

Guest

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
how do you identify a record in a recordset? 1
Access How to check that the value of a field in a new record is not present in 2 fields of the table? 0
How to refer to a control on a child form in the on close event of the master fo 3
How do you doubleclick on a recordset to open recordset in form vi 3
How do you hide Columns in a SubForm - based on values in the datasheet 1
How to have a variable in a recordset field name 1
How to loop through a recordset and update the field of a recordset or delete current record 1

Top