Add Record to Linked Table

[hgoslin]

Hi - help for a newbie
I have 2 separate databases, one tracks work done for the company by
various service providers, the 2nd database that I am creating tracks
the tendering process for work to be done for the company by existing
service providers as well as potential new service providers. The
requirement is that once a tender has been awarded, that service
provider name must be added to the first database (tracking work
done). I need to do the following: when the tender is awarded, check
wether the service provider exists in the 1st db, if not add their
name to the 1st db.

1st db Name: JHC Maintenance
Table storing Service providers names: ServiceProvider it only has one
field as it is a source list for a combo box, the field name
is:ServiceProvider

2nd db Name: Nicor Procurement
Table storing the names of successful tender service providers:
TenderDetails
Field: Lookup field on on another table:CompanyInformation,
referencing the CompanyID field, but displaying the CompanyName (1st
field is hidden in the lookup).

I have created an unbound control on the form: frmTenderDetails
called: txtCoName that uses DLookup to retrieve the company name as
text. I need to create a Command Button to check the ServiceProvider
table in DB1 for the existance of the Service Provider name, and if it
doesn't exist add it to that table. I have currently linked the
Service Provider table to DB 2. I am not sure where to go from here to
code the comand button. Would greatly apppreciate some help.
Thanks
Heather

 

[hgoslin]

Hi - help for a newbie
I have 2 separate databases, one tracks work done for the company by
various service providers, the 2nd database that I am creating tracks
the tendering process for work to be done for the company by existing
service providers as well as potential new service providers. The
requirement is that once a tender has been awarded, that service
provider name must be added to the first database (tracking work
done). I need to do the following: when the tender is awarded, check
wether the service provider exists in the 1st db, if not add their
name to the 1st db.

1st db Name: JHC Maintenance
Table storing Service providers names: ServiceProvider it only has one
field as it is a source list for a combo box, the field name
is:ServiceProvider

2nd db Name: Nicor Procurement
Table storing the names of successful tender service providers:
TenderDetails
Field: Lookup field on on another table:CompanyInformation,
referencing the CompanyID field, but displaying the CompanyName (1st
field is hidden in the lookup).

I have created an unbound control on the form: frmTenderDetails
called: txtCoName that uses DLookup to retrieve the company name as
text. I need to create a Command Button to check the ServiceProvider
table in DB1 for the existance of the Service Provider name, and if it
doesn't exist add it to that table. I have currently linked the
Service Provider table to DB 2. I am not sure where to go from here to
code the comand button. Would greatly apppreciate some help.
Thanks
Heather

I have currently tried the following code gleaned from searching this
group, however I get an Error 2046 that the FindRecord command is not
available:

Private Sub cmdAddMaint_Click()
Dim strCoName As String

strCoName = txtCoName
Set ServiceProviderRecordset = _
CurrentDb.OpenRecordset("ServiceProvider", dbOpenDynaset)

With ServiceProviderRecordset
If strCoName = "" Then
MsgBox "There is no company to add to the Maintenance
Database", , "Invalid Data"
Else

DoCmd.FindRecord strCoName, , True, , True
End If
If NoMatch Then
.AddNew
.Update
End If
End With

End Sub