Help with attaching a code to a button, please

[terbolee]

I have a form in which all the fields are "unbounded" to any table. Thru the use of other codes and data entry, the fields are now filled. I now need to save all the fields as a single record into a table. I believe I need a "Save" button that will run the code below.

Three questions:
1. Will the button be on the form itself or on the task bar above?
2. The code shows up in red in VB Editor...probably a syntax error...can you help?
3. How can I tie the code to the button?

Thanks.

=====================================
Private Sub Detail_Click()

Dim strSQL As String

strSQL = "INSERT INTO Invoices ([Organisation Code], [Organisation Type], [Organisation], [Organisation Phone], [Organisation Fax], [Department], [Street], [Suburb], [State], [Postcode], [Country], [Contact Title], [Contact First name], [Contact Surname], [Contact position], [Contact MOB], [Days in Period], [Invoice Period From], [Invoice Period To], [Invoice Date], [Invoice number], [Invoice Payment Terms], [Service-1 Description], [Service-1 Fee], [Pmt-1 Amt], [Pmt-1 Date], [Service-2 Description], [Service-2 Fee], [Pmt-2 Amt], [Pmt-2 Date], [Service-3 Description], [Service-3 Fee], [Pmt-3 Amt], [Pmt-3 Date], [Service-4 Description], [Service-4 Fee], [Pmt-4 Amt], [Pmt-4 Date], [Service-5 Description], [Service-5 Fee], [Pmt-5 Amt], [Pmt-5 Date], [Service-6 Description], [Service-6 Fee], [Pmt-6 Amt], [Pmt-6 Date], [Service-7 Description], [Service-7 Fee], [Pmt-7 Amt], [Pmt-7 Date], [Service-8 Description], [Service-8 Fee], [Pmt-8 Amt], [Pmt-8 Date], _
[Service-9 Description], [Service-9 Fee], [Pmt-9 Amt], [Pmt-9 Date], [Service-10 Description], [Service-10 Fee], [Pmt-10 Amt], [Pmt-10 Date]) _
VALUES ('" & [Organisation Code] & "','"& Me.[Organisation Type] & "','"& Me.Organisation & "','"& Me.[Organisation Phone] & "','"& Me.[Organisation Fax] & "','"& Me.Department & "','"& Me.Street & "','"& Me.Suburb & "','"& Me.State & "','"& Ms.Postcode & "','"& Me.Country & "','"& Me.[Contact Title] & "','"& Me.[Contact First Name] & "','"& Me.[Contact Surname] & "','"& Me.[Contact Position] & "','"& Me.[Contact MOB] & "','"& Me.[Days in Period] & "','"& Me.[Invoice Period From] & "','"& Me.[Invoice Period To] & "','"& Me.[Invoice date] & "','"& Me.[Invoice Number] & "','"& Me.[Invoice Payment Terms] & "','"& Me.[Service-1 Description] & "','"& Me.[Service-1 Fee] & "','"& Me.[Pmt-1 Amt] & "','"& Me.[Pmt-1 Date] & "','"& Me.[Service-2 Description] & "','"& Me.[Service-2 Fee] & "','"& Me.[Pmt-2 Amt] & "','"& Me.[Pmt-2 Date] & "','"& Me.[Service-3 Description] & "','"& Me.[Service-3 Fee] & "','"& Me.[Pmt-3 Amt] & "','"& Me.[Pmt-3 Date] & "','"& _
Me.[Service-4 Description] & "','"& Me.[Service-4 Fee] & "','"& Me.[Pmt-4 Amt] & "','"& Me.[Pmt-4 Date] & "','"& Me.[Service-5 Description] & "','"& Me.[Service-5 Fee] & "','"& Me.[Pmt-5 Amt] & "','"& Me.[Pmt-5 Date] & "','"& Me.[Service-6 Description] & "','"& Me.[Service-6 Fee] & "','"& Me.[Pmt-6 Amt] & "','"& Me.[Pmt-6 Date] & "','"& Me.[Service-7 Description] & "','"& Me.[Service-7 Fee] & "','"& Me.[Pmt-7 Amt] & "','"& Me.[Pmt-7 Date] & "','"& Me.[Service-8 Description] & "','"& Me.[Service-8 Fee] & "','"& Me.[Pmt-8 Amt] & "','"& Me.[Pmt-8 Date] & "','"& Me.[Service-9 Description] & "','"& Me.[Service-9 Fee] & "','"& Me.[Pmt-9 Amt] & "','"& Me.[Pmt-9 Date] & "','"& Me.[Service-10 Description] & "','"& Me.[Service-10 Fee] & "','"& Me.[Pmt-10 Amt] & "','"& Me.[Pmt-10 Date] & "','"& ");"

DoCmd.RunSQL strSQL

End Sub
================================

 

[Mile-O]

Your database would appear to be poorly designed and requires normalisation.

For example, your invoice table contains information that has no place there (such as address details) and you have a repeating group in that the fields are numbered consecutively. What happens if you need to add a further numbered item?

But, if you aren't willing to normalise your database to a usable structure, the question is why you don't just bind the invoice table to your form, since it would appear to be the obvious quick fix solution.

 

[Uncle Gizmo]

Shouldn't this: [Organisation Code]
be like this: Me.[Organisation Code]

An observation:
It appears you have set out all of your services in one table. You would be far better off allocating your services to a separate table, this would give you several advantages, notably that you would not be limited to just ten services you could have as many services as you wanted. Future database modifications would be much simpler, as it would follow the standard practices' employed by other database programmers.

Another example, in your database scenario, how will you search the services if required to do so? With the services in one table this would be a relatively easy task. However the way you have it setup it would be practically impossible.

 

[terbolee]

Thanks for your quick response.

The numbered items go up to 10...5 more than that is actually required...just in case. As such no further numbered items are needed.

There are other codes that are used to lookup fields from another table, embedded fields are used to create customized embedded fields. As such, I got the other codes working except for this one. To get this far, I've had to keep the form unbounded.

Now that the fields on the form are populated, saving it as a record into a table will later allow me to merge the data with MS-Word. This will also allow me to link the table to MS-Outlook to produce reminders.

So, if it's alright with you, I'd like your help to create a "Save" button to run the code.

When all's working, I will post all of my working codes here for others to see. Thanks.

Your database would appear to be poorly designed and requires normalisation.

For example, your invoice table contains information that has no place there (such as address details) and you have a repeating group in that the fields are numbered consecutively. What happens if you need to add a further numbered item?

But, if you aren't willing to normalise your database to a usable structure, the question is why you don't just bind the invoice table to your form, since it would appear to be the obvious quick fix solution.

 

[terbolee]

Many thanks for your observations. The reason for this "flat" design was because the services are rarely similar and there is no foreseeable need to search by the service. However, I chose this flat design to meet the limitations of the mail merge feature in MS-Word...which comes next.

Thanks for your advice on Me.[Organisation Code]. I will try that. Many thanks.

Shouldn't this: [Organisation Code]
be like this: Me.[Organisation Code]

An observation:
It appears you have set out all of your services in one table. You would be far better off allocating your services to a separate table, this would give you several advantages, notably that you would not be limited to just ten services you could have as many services as you wanted. Future database modifications would be much simpler, as it would follow the standard practices' employed by other database programmers.

Another example, in your database scenario, how will you search the services if required to do so? With the services in one table this would be a relatively easy task. However the way you have it setup it would be practically impossible.