Append SQL

Brogrim
Brogrim used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Could you think about an ON INSERT trigger perhaps.
BrogrimInformation Systems Development Manager

Author

Commented:
The only problem is that the value for the serviceid is comes from the Frontend
Top Expert 2012

Commented:
Please post the structure (CREATE TABLE) for your table.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
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.
BrogrimInformation Systems Development Manager

Author

Commented:
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
Commented:
It looks like you have an error in your query there, but it does look like you have the idea.  The number of columns in the first set of parens (MemberID, ServiceID) must match the second, but you have two in the first and three in the second (Member ID plus 4 twice).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial