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

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" wrote:

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" <Jack@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AEAD1B2F-ABD2-4A9A-8067-40556AC7C3B4@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: How do you display the values of a recordset in a message?
    ... sync, then only the first record is getting displayed in the message box. ... would like to display all the records in the messagebox. ... Dim TBS_rs As Recordset ... I have the following code that checks to see if the master table has ...
    (microsoft.public.access.modulesdaovba)
  • RE: macro
    ... The formula is represented as a string like: ... Dim anyFormula As String ... after the code copies the updated entries over to the Master sheet? ... Dim MasterSheet As String ' for name of the Master sheet ...
    (microsoft.public.excel.misc)
  • RE: macro
    ... The formula is represented as a string like: ... Dim anyFormula As String ... after the code copies the updated entries over to the Master sheet? ... Dim MasterSheet As String ' for name of the Master sheet ...
    (microsoft.public.excel.misc)
  • RE: macro
    ... The formula is represented as a string like: ... Dim anyFormula As String ... after the code copies the updated entries over to the Master sheet? ... Dim MasterSheet As String ' for name of the Master sheet ...
    (microsoft.public.excel.misc)
  • RE: macro
    ... the column identification letters for the data columns on each sheet. ... Dim MasterSheet As String ' for name of the Master sheet ... Dim CC_MColumn As String ' column with Cost Centre entries on the Master ...
    (microsoft.public.excel.misc)