DoCmd.RunMacro

[LiquidWrench]

I use this code in the click event of a cmd button and the append query runs
but appends no records.

Private Sub cmdTrackComplete_Click()
DoCmd.RunMacro "MCR-TrackComplete"
If chkTrackSuccessYes_no <> 0 Then
Me.cmdRecordRemove.Visible = True
Else: Me.cmdRecordRemove.Visible = False
End If
End Sub

When I run just the macro itself from the click event in properties for the
cmd button it works fine and appends the record. I really want to run it
from code so the If Then Else statement will also occur.

Does anyone know why the Append query that is called by the macro will not
append a record when the macro is run from code but when the same macro is
run from the properties/click event directly it will append a the current
record.

The specific message is "You are about to append 0 records"
It should read "You are about to append 1 record"

 

[Steve Sanford]

Since you are writing code, why don't you comvert the macro to VBA and add it
to your subroutine? Without seeing the macro steps or the converted VBA code,
it is difficult to troubleshoot your problem.

It looks like "chkTrackSuccessYes_no" is a boolean field. Instead of using

If chkTrackSuccessYes_no <> 0 Then
Me.cmdRecordRemove.Visible = True
Else: Me.cmdRecordRemove.Visible = False
End If

you could use

Me.cmdRecordRemove.Visible = Me.chkTrackSuccessYes_no


HTH

 

[LiquidWrench]

The macro named MCR-TrackComplete steps are as follows

OpenQuery---QRY-TrackComplete, Datasheet, Edit

Here also is the SQL for the Append query.

INSERT INTO [Track Completed] ( LastName, FirstName, EmailAddress, JobTitle,
HomePhone, MobilePhone, Address, City, StateProvince, PostalCode,
CountryRegion, WebPage, Notes, TrackStartDate, Paslet1ActualDate,
WCardActualDate, WCallActualDate, DOZActualDate, TransActualDate,
HBSActualDate, Paslet2ActualDate, TrackCompleteDate, TrackSuccessYesNo )
SELECT [New Contacts].LastName, [New Contacts].FirstName, [New
Contacts].EmailAddress, [New Contacts].JobTitle, [New Contacts].HomePhone,
[New Contacts].MobilePhone, [New Contacts].Address, [New Contacts].City, [New
Contacts].StateProvince, [New Contacts].PostalCode, [New
Contacts].CountryRegion, [New Contacts].WebPage, [New Contacts].Notes, [New
Contacts].TrackStartDate, [New Contacts].Paslet1ActualDate, [New
Contacts].WCardActualDate, [New Contacts].WCallActualDate, [New
Contacts].DOZActualDate, [New Contacts].TransActualDate, [New
Contacts].HBSActualDate, [New Contacts].Paslet2ActualDate, [New
Contacts].TrackCompleteDate, [New Contacts].TrackSuccessYesNo
FROM [New Contacts]
WHERE ((([New Contacts].TrackSuccessYesNo)<>0));

And Yes chkTrackSuccessYes_No is a boolean.

I tried to just write the SQL in VB code but apparently am not doing it right.
Is there that much of a difference between VB6 and VB in Access 2007 ?

 

[Dirk Goldgar]

I use this code in the click event of a cmd button and the append query
runs
but appends no records.

Private Sub cmdTrackComplete_Click()
DoCmd.RunMacro "MCR-TrackComplete"
If chkTrackSuccessYes_no <> 0 Then
Me.cmdRecordRemove.Visible = True
Else: Me.cmdRecordRemove.Visible = False
End If
End Sub

When I run just the macro itself from the click event in properties for
the
cmd button it works fine and appends the record. I really want to run it
from code so the If Then Else statement will also occur.

Does anyone know why the Append query that is called by the macro will not
append a record when the macro is run from code but when the same macro is
run from the properties/click event directly it will append a the current
record.

The specific message is "You are about to append 0 records"
It should read "You are about to append 1 record"

That message implies that the criteria for the query are not met. Looking
at the query SQL that you posted in a later message, I see that the query's
WHERE clause is:

WHERE ((([New Contacts].TrackSuccessYesNo)<>0))

So this implies that there are no records for which TrackSuccessYesNo is
True. Is your checkbox control, "chkTrackSuccessYes_no", bound to this
field in the table? If so, my guess would be that you haven't yet saved the
record after checking the box, so the check box is True but the field in the
record, in the table, is still False.

If that's the problem, then all you need to do is save the record before
executing the macro:

Private Sub cmdTrackComplete_Click()

' Force record save.
If Me.Dirty Then Me.Dirty = False

' Run append query.
DoCmd.RunMacro "MCR-TrackComplete"

' Show/hide button.
Me.cmdRecordRemove.Visible = Me.chkTrackSuccessYes_no

End Sub