Copy Information from one table into antoher in .adp

[Guest]

Hi everybody,
I'm using a .adp front-end and SQL 2000 server database. I have a form based
on table Companies and its subform based on contacts. I need to keep track
who is the Main contact for the Company. What I've been trying to do is to
update a view based on the two tables and to copy from one field to the other
based on the company ID. I did something really wrong because it is looking
and looking one by one.... Help!!!

Thanks in advance

Private Sub MainContactValue_BeforeUpdate(Cancel As Integer)

' check if FName, LName is empty, message (can't be blank and selected)
If IsNothing(Me.LName) Then
MsgBox "You must Enter a Contact before you can set Default.", _
vbCritical, gstrAppTitle
'Cancel = True
Exit Sub
End If

'check if somebody else is already the main contact and message to uncheck
If (Me.MainContactValue = True) Then
' Try to lookup another contact set Default
If Not IsNothing(DLookup("Contact_ID", "Companies", _
"Company_ID = " & Me.Parent.Company_ID & _
" AND Contact_ID <> " & Me.Contact_ID)) Then
' ooops...
MsgBox "You have designated another contact as the Default for
this Company." & _
" You must remove that designation before you can mark this
Contact as the Default.", _
vbCritical, gstrAppTitle
'Cancel = True

End If

Else
On Error GoTo cmdAll_Error
CurrentProject.Connection.Execute "UPDATE qryCompaniesContactsupdate
SET MainContact = MainContactName, ContactIDCo = Me.Contact_ID" & _
" WHERE Company_ID = " & Me.Company_ID, , adCmdText +
adExecuteNoRecords
' Refresh to show the result
Me.Refresh
' Done, exit
cmdAll_Exit:
' Exit Sub

cmdAll_Error:
' Something went bad with the update
MsgBox "Updating Contact failed.", vbCritical, gstrAppTitle
' Log the error
ErrorLog Me.Name & "_cmdAll", Err, Error
Resume cmdAll_Exit
End If
End Sub

 

[Sylvain Lafontaine]

What do you mean by « it is looking and looking one by one » ?

If MainContactName is a character string, then you have to delimit it with
single or double quotes in the Update statement.

 

[Guest]

Thanks for asking Sylvain,
I've figure it out what I was doing wrong... once I've set WHERE to match
the contact_ID it goes straight to the record and updates the fields..
Live and learn. Thank God for this Dicussion Group. When there is no hope
somebody asks the same quesion or answers/sees your wrongdoing.

Thanks again, "see" you around