Append query doesn't add data in macro (?)

[ttellis]

I have a database that when new data is added, a number of things should happen. Employee data will be added to table BadgeData, certain information from the data entry form and table BadgeData should append to table Updates, and a report should print. I used a macro attached to a command button to accomplish this. The append query that I'm using works fine in a stand-alone mode, but does not work (and does not error out) when running it in the macro. Can someone tell me what I'm missing?:

INSERT INTO Updates ( CardNum, LastReauth, AutoNum )
SELECT BadgeData.CardNum, BadgeData.RequestDate, BadgeData.AutoNum
FROM BadgeData
WHERE (((BadgeData.CardNum)=Forms!NewBadgeRequest1!CardNum));

Tiffany

 

[sfreeman]

Looks good to me. Code works fine, stand alone or by the macro. What macro command are you using, OpenQuery?

Maybe you should not use the macro, convert it to VBA, something like:
'------------------------------------------------------------
' BadgeData
'
'------------------------------------------------------------
Function BadgeData()
On Error GoTo BadgeData_Err
Dim strSQl As String

DoCmd.SetWarnings False

strSQl = "INSERT INTO Updates ( CardNum, LastReauth, AutoNum )SELECT BadgeData.CardNum, BadgeData.RequestDate, BadgeData.AutoNum FROM BadgeData WHERE (((BadgeData.CardNum)=[Forms]![NewBadgeRequest1]![CardNum]));"
DoCmd.RunSQL strSQl

DoCmd.SetWarnings True

BadgeData_Exit:
Exit Function

BadgeData_Err:
MsgBox Error$
Resume BadgeData_Exit

End Function

HTH

 

[ttellis]

sfreeman,
I tried what you suggested and it still does not work. When running it within the macro, yes, I'm using OpenQuery. Could it possibly be a violation in the tables' relationships??

 

[JonFer]

Change it to a select query and run it from the macro. Does it show any records? If so, make sure Warnings are on and then run it as an append again. You should get a message for how many rows will be appended and then one for the errors preventing any rows from being appended.


John

http://www.interautomateconsulting.com

 

[ttellis]

Change it to a select query and run it from the macro. Does it show any records?

No, when the query runs within the macro, no records are selected.

 

[JonFer]

Did the query open but show no records when run from the macro? If you then open the Select query manually, does it work? If it does work manually, you can try adding the [Forms] parameter to the Parameter list for the query - should not be necessary but...

If the query didn't open at all from the macro, do you have a condition set for that macro step? If the condition is not an explicit check for NULLS but the value being checked is a NULL, the macro can stop without any error displaying. Remove the condition if you have one to check if that is the problem.



John

http://www.interautomateconsulting.com

 

[ttellis]

Correct, the query shows no records when ran from the macro, but provides the specified record when ran manually. I'm starting to thing that there's somthing missing in the macro. What do you think??

'------------------------------------------------------------
' SubmitApprovalForm
'
'------------------------------------------------------------
Function SubmitApprovalForm()
On Error GoTo SubmitApprovalForm_Err

    ' Hourglass
    DoCmd.Hourglass True
    DoCmd.SelectObject acForm, "NewBadgeRequest1", False
    DoCmd.GoToControl "CardNum"
    ' Open Approval form
    DoCmd.OpenReport "ApprovalForm", acViewPreview, "ApprovalForm Query", "[BadgeData]![Date Entered]=[Forms]![NewBadgeRequest1]![Date Entered]", acNormal
    ' Run UpdateAtSubmit Query
    DoCmd.OpenQuery "UpdateAtSubmit Query", acViewNormal, acEdit
    ' Close new badge form
    DoCmd.Close acForm, "NewBadgeRequest1"
    ' message
    Beep
    MsgBox "New Badge requests have been sent to Data Security & Controls. Please print approval forms.", vbInformation, "New Badge Request Received"


SubmitApprovalForm_Exit:
    Exit Function

SubmitApprovalForm_Err:
    MsgBox Error$
    Resume SubmitApprovalForm_Exit

End Function

 

[JonFer]

I thought you meant an Access macro not a VBA module. Try these steps for running the query:

Dim qd1 as DAO.QueryDef

' Run UpdateAtSubmit Query
Set qd1 = CurrentDb.QueryDefs("UpdateAtSubmit Query")
qd1.Parameters(0)=Forms!NewBadgeRequest1!CardNum
qd1.Execute
Msgbox qd1.RecordsAffected & " records updated/appended."
Set qd1 = Nothing

John

http://www.interautomateconsulting.com

 

[ttellis]

I [bold]did[/bold] mean an Access macro. I only converted it to a module in order to post it here on the forum. I really hate to sound so dim-witted, but I have no idea what your last suggestion means.

 

[JonFer]

Well then...never mind about that code. However, what about my other questions on the macro? Is there a condition set for that macro step? Why don't you add a MSGBOX step right before the OpenQuery that just displays the value of Forms!NewBadgeRequest1!CardNum so you can verify there is really a value there.

You can also create a Function that returns the value of the form control (save it in a module) and use that in your query instead:

Public Function ReturnFormValue() as Long

   ' should have some error checking
   ' but for now just make sure the form is open
   ReturnFormValue = Forms!NewBadgeRequest1!CardNum

End Function

Where clause:
WHERE BadgeData.CardNum=ReturnFormValue();


John

http://www.interautomateconsulting.com

 

[ttellis]

I will try this. Will this work if the form contais more than one value for CardNum. The form that this data is coming from is a continuous form and may have more than one record on each form.

 

[JonFer]

3rd time: Is there a condition for the macro step that opens the query? If so, what is it?


John

http://www.interautomateconsulting.com

 

[ttellis]

I'm so sorry...no...no conditions

 

[ttellis]

All - After hours of going through some more posts on the forum I found a thread that really helped me figure out what I was missing (See Thread701-909427). Here is my new query (SQL view):

INSERT INTO Updates ( CardNum, AutoNum, LastReauth )
SELECT BadgeData.CardNum, BadgeData.AutoNum, BadgeData.RequestDate
FROM BadgeData
WHERE (((BadgeData.AutoNum) Not In (SELECT AutoNum FROM Updates )));

The "Not In" statement is what I was missing. Thanks to you both for all of your help!