Form help please

[007stuart]

I have a form that allows a new address to be added to my databses. It consists of 4 text boxes and 5 Combo boxes. I have written "not in list" events to allow new data to be added to each of the combo boxes however if new data is added to one of my Combo boxes (CboPostCode) I need to then add more information to TblPostCode to complete the address record. I have used the "after update" event to run a macro that opens FrmPostCode at the last record created (the ID from the Postcode added in the FrmNewaddress) however I do not want the macro to run if the new record created uses a postcode that is already recorded.

Can anybody help me achieve this?

Many thanks

 

[James Dudden]

Hi,
You need to open a recordset and check through it to see if there are any matches.

You could run the code on the BeforeUpdate - something like this:

Private Sub BeforeUpdate
Set rs = db.OpenRecordset(SELECT Postcode WHERE postcode = me!postcode)

If rs.RecordCount <> 0 Then
Me.Undo
MsgBox ("There is already a record with this postcode")
End If
End Sub

This is very rough and would need the correct query string and field names but you get the idea.

Regards
James

 

[007stuart]

Thanks for pointing me in that direction, I can understand where you are going with the code.

I have created a query which looks at my TblPostCode called QryPostcode. The SQL behind the Query is SELECT TblPostCode.PostCode
FROM TblPostCode
WHERE (((TblPostCode.PostCode)=[me].[postcode]));.

the 1st line of my code Set rs = db.openrecordset(QryPostcode).
I then used the rest of your code however I get a Runtime error 404 Object required when I enter an existing postcode or if a new Postcode is added, my "not in list" event runs ok but again stops with error 404.

my knowledge is very limited so a further point in the right direction would be appreciated.

 

[James Dudden]

What line do you get the error on?
It may be helpful if you post a screenshot or paste your code with the line highlighted where it errors.

 

[007stuart]

Here is capy of the code:



Private Sub CboPostCode_BeforeUpdate(Cancel As Integer)
Set rs = db.openrecordset(QryPostcode)
If rs.RecordCount <> 0 Then
Me.Undo
MsgBox ("There is already a record with this postcode")
End If

End Sub

The Line highlighted in red has a yellow background

I have also attached a small Zip file

 

[James Dudden]

You need to set rs as a variable. Add the below code above set rs =

Dim rs as Recordset

 

[007stuart]

I still get error 404

 

[James Dudden]

Ok, I have had a quick look at your database and i'm getting a little confused. Is this just part of it? If so could you send the whole thing so i can figure out what needs to happen?

Also when you say you - don't want the macro to run if the new record created uses a postcode that is already recorded - do you mean in the adress records table or in the postcodes table?

 

[007stuart]

Thanks for taking the time to look at this. i have included the missing detail

 

[James Dudden]

Could you also confirm where the postcode should not be duplicated - is it in the postcode table or is it in the propertyinfo table?

 

[007stuart]

In the Propertyinfo table

 

[James Dudden]

Stuart,
I have had a quick look at your database and I am sorry to have to say this but your problem is bigger than you think. There is a lot fundamentally wrong with it.

To start with it is always best practise to have a spilt front end and back end. Second, you don’t have any lookups on your tables so there is no way of matching the Postcode to the PropertyInfo table. If you open the PropertyInfo in datasheet view and try and type in a Postcode you will see what I mean – it is only connected via the ID.

You need to get rid of your relationships and then open the table in design view and add some lookups. The easiest way is to select the Lookup Wizard from the Data type and follow the instructions then you can go back and enforce all of your relationships.

Another point to consider is if the Postcode is going to be unique you could use that as the Primary Key.

I feel these issues need to be addressed before a solution can be found. With that in mind I would suggest re-creating the database and the re-post if you are still having problems.

Regards

JD

 

[007stuart]

Thanks for your help.

I will take on board your recommendation and see where I go from there.

Stuart