VBA Dlookup Type Mismatch

[steve21nj]

Hello,

I am having an issue with my vba while following Dev Ashish's site http://access.mvps.org/access/general/gen0018.htm

My Dlookup works correctly, from what I have tested. The issue comes from when I run the update query after, it updates every record instead of the current record. Can anyone point me in the right direction?

The after update vba: (keep in mind MemberID is a text field)

Private Sub Combo59_AfterUpdate()
Response = MsgBox("Do you want update this individuals program?", vbYesNo + vbQuestion, "Update Program")
 
If Response = vbYes Then
 
    If DLookup("MemberID", "tblMemberProgram", "[MemberID] = '" & [Forms]![FRM_Member_Stats].[MemberID] & "'") > 0 Then
        'MsgBox "Record exist"
       DoCmd.OpenQuery "UpdateProgram"
    Else
        MsgBox "Record does not exist"
      '  DoCmd.OpenQuery "AppendProgram"
    End If
End If
End Sub

Update Query

UPDATE tblMemberProgram SET tblMemberProgram.ProgramOffice = [Forms].[FRM_Member_Stats].[Combo59];

Do I need to run the dlookup in the update query to limit to the specific record based on the form?

 

[steve21nj]

think I figured it out unless someone has a better response...

UPDATE tblMemberProgram SET tblMemberProgram.ProgramOffice = [Forms].[FRM_Member_Stats].[Combo59]
WHERE ((([Forms].[FRM_Member_Stats].[Text44])=[tblMemberProgram].[MemberID]));

 

[steve21nj]

Then it brings me to my next issue on the append side...

When I append the current record, it sees that the table has 3 records, and adds 3 of the same current record. So then it has 6 records in the table. If I run it again, it adds 6 records and so on.

INSERT INTO tblMemberProgram ( MemberID, ProgramOffice )
SELECT [Forms].[FRM_Member_Stats].[Text44] AS Expr2, [Forms].[FRM_Member_Stats].[Combo59] AS Expr1
FROM tblMemberProgram;

 

[plog]

If you only want to insert data from the form then your SQL statement shouldn't be using a SELECT query FROM tblMemberProgram.

INSERT INTO (FieldName1, FieldName2, ...) VALUES (Value1, Value2, ...)

http://www.w3schools.com/sql/sql_insert.asp

 

[steve21nj]

Thanks for the help. My end product.

Append

INSERT INTO tblMemberProgram ( MemberID, ProgramOffice )
SELECT [Forms]![FRM_Member_Stats].[Text44] AS Expr1, [Forms]![FRM_Member_Stats].[Combo59] AS Expr2;

Update

UPDATE tblMemberProgram SET tblMemberProgram.ProgramOffice = [Forms].[FRM_Member_Stats].[Combo59]
WHERE ((([MemberID] = [Forms].[FRM_Member_Stats].[Text44])));