View Single Post
Old March 29th, 2004, 03:08 PM
jurgenw jurgenw is offline
Authorized User
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts

If you use the QBE interface to design the append query and save it, the syntax you used will prompt the user to key in an RFQNumber.

What you need to do then is write code to respond to some event to execute the query. If you are going to use the execute method of the database object (DAO, you need the connection object for ADO), the user will not be notified of the fact that a record is about to be added. In that case, you should create a database variable, run the execute against the variable and check the database variable's recordsaffected property (you can't do this with an execute against currenttdb) to determine whether the append succeeded.

One other gotcha with what you've got there: Access 97 has an issue with decompiling when there are more than 10 consecutive line continuations. I do not know that this has ever been addressed in subsequent versions so I always keep it down to 8 continuations just in case. In your case, you would need to have a string variable and concatenate to it to keep the line continuations down to a reasonable number.

Dim strSql

strSql = "blah " & _
         "more blah " & _
         "even more " & _
         "still yet more "
strSql = strSql & "yet again more " & _
         "finally done"

You may also need to use delimiters if your RFQNumber is a string.

Given the ugly length of the syntax, it may be easier to open a recordset and addnew. My preference is to use an insert as has been suggested here but sometimes maintenance takes precedence:

Dim rstFrom As Recordset
Dim rstTo As Recordset
Dim fld As Field
Dim lngKey As Long
Dim db As DAO.Database

lngKey = InputBox ("Select an RFQ to copy")
Set rstFrom = ("Select * From RFQInputSheet Where RFQNumber = " & lngKey")
If rstFrom.EOF Then
    Msgbox "No match in From Table"
    Set rstTo = ("Select * From HoldTable Where RFQNumber = " & lngKey)
    If rstTo.EOF Then
        For Each fld in rstTo.Fields
            rstTo(fld.Name) = rstFrom(fld.Name)
        MsgBox "Already exists"
    End If
End If
Set rstFrom = Nothing
Set rstTo = Nothing
Set db = Nothing

J├╝rgen Welz
Edmonton AB Canada