Form Validation

[wfoster1]

I am creating a form based on a table where there is no
"real" primary key available (SS#, Tel#)(I used autonumber). The 2 underlying tables are a "bldgs" table and and "Res(ident)Data" Table. The relationship between them is 1 - many so that there will be many residents living in one building but a resident may only live in one building. The primary key in bldgs and foreign key in Resdata is BldgAddress. On the ResData form the bldgAddress is in a combobox and must be taken from list (from bldg table). My concern is that there will be 2 "John Smith" entries for the same person in the same bldg

Since there is no natural primary key in Resdata, my goal is to create a procedure that will take the BldgAddress, Lastname,firstname & Apt fields from the ResData form and run multiple IF statements ie.

Dim Rs As Recordset, Db As Database
Set Db = CurrentDb()

Set Rs = Db.OpenRecordset("tblResidentdata&quot

If rs.bldgaddress = me.bldgaddress then
If rs.lastname = me.lastname then
If rs.firstname = me.firstname then
If rs.Apt = me.apt then
Msgbox ("There is already a John Smith living at 1313 Mockingbird Lane Apt 4, Are you sure you want to add this entry?"

Am i on the right track?? I received a object not in collection message so .....

Any help is appreciated

WFoster

 

[scking]

Use the bang ! operator to reference a field in the recordset (rs!firstname) and give it another try. While I may not agree with the original design I believe your approach will work. It may be more clear, rather than using so many nested if statements, to construct it as follows.

With rs
If ((.bldgaddress = me.bldgaddress) And _
(.lastname = me.lastname) And _
(.firstname = me.firstname) And _
(.Apt = me.apt)) then
MsgBox "Whatever"
End If
End With

Steve King


Growth follows a healthy professional curiosity