Link to home
Start Free TrialLog in
Avatar of Brogrim
BrogrimFlag for Ireland

asked on

Append SQL

I want to append data into 2 colums

MemberID
ServiceID

I have 4 checkbox on a form when they are checked trueI want to append the MemberID to a table.

That leaves the ServiceID null,

If check box A is true 1 want the serviceID to be 1
If check box B is true 1 want the serviceID to be 2
If check box C is true 1 want the serviceID to be 3
If check box D is true 1 want the serviceID to be4

i cannot use an update after the record insertion because I want to create a unique index.

I thought of changing teh default value bit I am using a SQL server 2005 back end with an MS Access 2010 front end
Avatar of Tony303
Tony303
Flag of New Zealand image

Could you think about an ON INSERT trigger perhaps.
Avatar of Brogrim

ASKER

The only problem is that the value for the serviceid is comes from the Frontend
Please post the structure (CREATE TABLE) for your table.
You can still use the AfterUpdate event on each checkbox control to insert your record... The fact that event is being called doesn't mean that a record has been inserted anywhere or by anything.

so for example:


Private Sub CheckboxA_AfterUpdate()
    Dim strSQL As String
    
    strSQL = "INSERT INTO MyTable (MemberID, ServiceID) VALUES (" & Me.txtMemberID & ", 1)"
    CurrentDb.Execute strSQL
End Sub

Open in new window


(I didn't see where you were getting MemberID, but in that example it'd be coming from a textbox on your form, if it's an Autonumber field in MyTable you'd just drop it from the field and value lists so it would be

INSERT INTO MyTable (ServiceID) VALUES (1)

Now there's no error handling and this is just the sample for checkbox A (put a ,2 ,3 ,4 for B C and D respectively) but that's the idea.
Avatar of Brogrim

ASKER

Sorry about the delay

I have got teh idea and wriitem some code

Private Sub ROC_AfterUpdate()

    If ROC = True And Post = True Then

        Dim strSQL As String
   
            strSQL = "INSERT INTO tblMember_Services_Contact Type (MemberID, ServiceID) VALUES (" & Me.MemberID & ", 4, 4)"
            CurrentDb.Execute strSQL

    End If

End Sub
ASKER CERTIFIED SOLUTION
Avatar of kmslogic
kmslogic
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial