Avatar of Brogrim
Brogrim
Flag 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
Microsoft SQL ServerMicrosoft Access

Avatar of undefined
Last Comment
kmslogic

8/22/2022 - Mon
Tony303

Could you think about an ON INSERT trigger perhaps.
Brogrim

ASKER
The only problem is that the value for the serviceid is comes from the Frontend
Anthony Perkins

Please post the structure (CREATE TABLE) for your table.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
kmslogic

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.
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
kmslogic

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question