Two tables, one form

  • Thread starter Thread starter PeterM
  • Start date Start date
P

PeterM

I have a form that I created in AC2003. This form will merge info from two
tables. So I assigned Table1 as the source for the form, inserted all of the
columns from Table 1. Next, I changed to source for the form to Table2 and
again inserted all of the columns from Table2 onto the form. I then changed
the name of all Table2 columns to precede it with an "n". i.e. FirstName
became nFirstName. All source for those items for Table2 were changed to
have no source. Again, the source for the form is Table1.

I lookup a row in Table1 in Table2 and if a match is found, I manually move
all Table2 items to their associated field (nFirstName) via VBA both for the
lookup and the move. When this happens Access complains that "Cannot Update.
Database or object is read-only"... Why? The items for Table2 have no
source.
 
On Tue, 9 Feb 2010 22:22:01 -0800, PeterM

Show us the code, stripped to the bare essentials. So far what you're
describing is convoluted but not wrong.

-Tom.
Microsoft Access MVP
 
the table Addreses is Table 2 in my description. It is not bound to the
form. Table1 is Contacts created by Outlook. The following code executes on
the current event of Contacts(Table1). It craps out on the following line.
AddressesInfo!FirstName = Me.nFirstName

Private Sub LookupAddress()
Dim dbMDS As Database
Dim AddressesInfo As Recordset
Dim errLoop As Error
Set dbMDS = Application.CurrentDb
Set AddressesInfo = dbMDS.OpenRecordset _
("select *" _
& " FROM Addresses " _
& " WHERE firstname = '" & Me.FirstName & "'" _
& " and lastname = '" & Me.LastName & "'", dbOpenForwardOnly)
If AddressesInfo.EOF = True Then
ClearAddresses
Exit Sub
End If
Do While Not AddressesInfo.EOF
AddressesInfo!FirstName = Me.nFirstName
AddressesInfo!LastName = Me.nLastName
AddressesInfo!SpouseName = Me.nSpouseName
AddressesInfo!Address = Me.nAddress
AddressesInfo!City = Me.nCity
AddressesInfo!StateOrProvince = Me.nStateOrProvince
AddressesInfo!PostalCode = Me.nPostalCode
' AddressesInfo!Country = Me.nCountry
AddressesInfo!WorkEmailAddress = Me.nWorkEmailAddress
AddressesInfo!HomeEmailAddress = Me.nHomeEmailAddress
AddressesInfo!HomePhone = Me.nHomePhone
AddressesInfo!WorkPhone = Me.nWorkPhone
AddressesInfo!WorkExtension = Me.nWorkExtension
AddressesInfo!MobilePhone = Me.nMobilePhone
AddressesInfo!FaxNumber = Me.nFaxNumber
AddressesInfo!Birthdate = Me.nBirthdate
AddressesInfo!Anniversary = Me.nAnniversary
AddressesInfo!Nickname = Me.nNickname
AddressesInfo!Profession = Me.nProfession
AddressesInfo!MedicalSpeciality = Me.nMedicalSpeciality
AddressesInfo!HospitalAffiliation = Me.nHospitalAffiliation
AddressesInfo!Notes = Me.nNotes
AddressesInfo!ProfessionalNotes = Me.nProfessionalNotes

AddressesInfo.MoveNext
Loop

End Sub
 
the table Addreses is Table 2 in my description. It is not bound to the
form. Table1 is Contacts created by Outlook. The following code executes on
the current event of Contacts(Table1). It craps out on the following line.
AddressesInfo!FirstName = Me.nFirstName
Click to expand...

You're opening the recordset with

("select *" _
& " FROM Addresses " _
& " WHERE firstname = '" & Me.FirstName & "'" _
& " and lastname = '" & Me.LastName & "'", dbOpenForwardOnly)

Try dbOpenDynaset to get an updateable recordset.

I'd really suggest using a Form with a Subform rather than all these unbound
textboxes though, probably with some code to run an Append or update query on
the selected record(s).
 
Back
Top