View Single Post
  #7 (permalink)  
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
Default

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"
Else
    Set rstTo = ("Select * From HoldTable Where RFQNumber = " & lngKey)
    If rstTo.EOF Then
        rstTo.Addnew
        For Each fld in rstTo.Fields
            rstTo(fld.Name) = rstFrom(fld.Name)
        Next
        rstTo.Update
    Else
        MsgBox "Already exists"
    End If
End If
rstFrom.Close
Set rstFrom = Nothing
rstTo.Close
Set rstTo = Nothing
Set db = Nothing

Ciao
J├╝rgen Welz
Edmonton AB Canada
jwelz@hotmail.com
Reply With Quote