I got an SQL error.

[foody]

I have entered the following code below, but when I run it it doesn't work I a get an error:

Public Sub ExportOrderDestination(TableName As String, intNumber As String, YourOrderNumber As String, SalesRep As String, JobNumber As String, Terms As String, inNumber As Integer, valueOrderNumber As String, valueSalesRep As String, valueJobNumber As String, valueTerms As String)
DoCmd.SetWarnings False
Dim strSQL As String
Dim recordSet As Database
Dim strSQLTwo
strSQL = "INSERT INTO [" & TableName & "] ([" & intNumber & "],[" & YourOrderNumber & "],[" & SalesRep & "],[" & JobNumber & "],[" & Terms & "]) VALUES (" & "SELECT invoiceID FROM Invoice-Address WHERE CustomerNumber = " & inNumber & ",'" & valueOrderNumber & "','" & valueSalesRep & "','" & valueJobNumber & "','" & valueTerms & "')"
MsgBox strSQL
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub

Any help would be greatly appreciate it.

 

[reclusivemonkey]

Try removing all your square brackets ([,]). That should help.

 

[foody]

I know you guys are trying very hard to help, but this is not a helpful answer. I guess I have to figure it out on my own. Thanks for the help though.

 

[Jibbadiah]

A sample insert statement would look like this:

strSQL = "insert into table_name values ('7725', 'Bob', 'Nice bloke');"

If you are only inserting into some of the columns you will need to specify the column names,

strSQL = "insert into table_name (column1, column3, column7) values ('7725', 'Bob', 'Nice bloke');"

Otherwise leave them out (no need to complicate things). You shouldn't need to be stipulating your column names through paramters... just put them directly into the sql.

An example were you insert values through a recordset:

strSQL = "insert into table_name values ('" & sPersonId & "','" & sPersonName & "','" & sPersonDescription & "');"

An example where you insert using a SQL statement (like yours) is:

strSQL = "insert into " & strTableName & " (PersonID, PersonName, PersonDescription)SELECT tbl.PId, tbl.PName, tbl.Desc FROM ThisTable tbl;"

HTH

James

 

[foody]

But what I am looking for here is a great example:
I have Table A, with three columns.

Insert Into Table A (Column1, Column2, Colum3) VALUES (X, Y,Z)

I already know what is Y and Z let us say Y and Z is 1 and 2, but I want to get the value X if a certain condition is met.
Say that condition is W = F.

So how do you do that?

You cannot do this? Insert into Table A (Column1, Column2, Column3) VALUES (X, 1, 2) Where W = F. Let us assume F = 10 from the parameter obtained from the function I created and that will make X = 15 so when the DOCMD.RUNSQL works
it would have the following result

Insert into Table A (Column1, Column2, Column3) VALUES (15, 1, 2) and bang
but I know that the above is an example so that you guys know what I am looking for. I hope this was explained the best of my abilities. Now the question is, HOW DO I do that!? I don't think the above example I mentioned will work, but I used that to tell you what I am looking for.

 

[Jibbadiah]

Not sure about a nestled select in an insert statement... maybe...

Otherwise you could get the value through a recordset and then use the parameter value in your insert statement.

Dim dbs As Database
Dim rst As Recordset
Dim strInvoiceId As String

Set dbs = CurrentDb
rst = dbs.OpenRecordset("SELECT invoiceID FROM tblInvoiceAddress WHERE CustomerNumber = " & inNumber & ";")

    With rst
        .MoveFirst
        strInvoiceId = rst("invoiceid")
    End With

rst.Close
dbs.Close

Then use the value for strInvoiceID in the insert statement.

J.

 

[Jibbadiah]

Oops... missed the "Set" keyword in the following line...

Set rst = dbs.OpenRecordset("SELECT invoiceID FROM tblInvoiceAddress WHERE ....)

 

[foody]

Thank yooooooooooooooooooooooouu!!! * gives you a very tight kisss on the cheeks * I have not tried it yet I will tell you right away the result but that is exactly I wanted!!!

 

[foody]

It worked finally!!!! Thank you so much One final please, if I have unbound box (it have to be unbound) can I set the control source to link to another column in entirely different table or form? I have done thins =[Invoice-Address]!InvoiceID but I get ?#Name as an error instead of the value. What do you suggest?

I am using the form Estimate and I want to take the InvoiceID from the table Invoice-Address.

 

[Jibbadiah]

Pleased to hear it Foody.

It is possible to do what you want.

Go to the properties tab of the unbound control.

Row Source Type should be set to "table/query"
Row Source is a SQL statement that will return the records you need.
i.e. SELECT Invoice-Address.InvoiceID FROM Invoice-Address;

Note: I have been told that you should try not to include weird characters in any of your naming conventions. Take a look at the following:http://www.mvps.org/access/general/gen0012.htm

HTH

James