How do I limit the number of records with a specific value, but allow multiple of other values?

[TazoTazmaniac]

Hello access gurus

I'm fairly new at coding and I would like to find a way to do the following:

Basically, I have two tables; a main one with the name and number (etc) of a client, and a second table with all the addresses in it. There's a column in the address table called 'Status' and the two lookup values are 'Primary' and 'Inactive'.

How do I make it so that I can only have one primary address per customer and possible multiple 'inactive' addresses? I am willing to give more details if needed, but I have no clue where to start with this one.

Thanks in advance!

[pbaldy]

You can use a DCount() or open a recordset. I just put something similar in a db; here's the first part:

Set db = CurrentDb()
  'get any records for this driver that are still active
  strSQL = "SELECT * FROM tblCleanEnergyCards " _
         & "WHERE DriverNumber = '" & Me.DriverNumber & "' AND CardActive <> 0"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

  If Not rs.EOF Then
     msg = "Driver already has a card; inactivate any old cards?"

[TazoTazmaniac]

Thank you for the answer, pbaldy. As I'm so new to coding, I'm a bit stuck! I can understand some of your coding, but not all of it! What does the following line mean before the message box?:

If Not rs.EOF Then

Plus, would I need the 'Set db' line if I only have one database?

Sorry if they're really simple questions - please be patient with me

[TazoTazmaniac]

Scrap my last post; I've tried editing the coding that you gave as an example and also tried DCount() but I seem to understand neither.

Could anyone give an example of what I could put in my database? I've attached a stripped down sample of my database - the form in question is frmClientDonorContact, from tables tblClientDonorContact and tblAddresses.
Client, Stock and Appointment Database_Status.zip

[pbaldy]

Offhand I don't see the attempt at the recordset or DCount(); where is it so I can fix it?

[TazoTazmaniac]

Hi pbaldy,

That's because I got so confused it isn't in there!

This is the SQL code I have written to find any records that are set as primary, but this is as far as I've got where it has worked and I don't even know if I'm on the right lines!

SELECT tblAddresses.AddressID, tblAddresses.ContactID, tblAddresses.Address1, tblAddresses.Address2, tblAddresses.Area, tblAddresses.Postcode, tblAddresses.Status FROM tblAddresses WHERE (((tblAddresses.ContactID)=forms!frmClientDonorContact!ContactID) AND ((tblAddresses.Status)= "Primary"));

No idea where to go next though.

Sorry about the delay in replies - I'm doing it at work and couldn't get to it until now.

[TazoTazmaniac]

Thank you for your help, but I've now given up and have just used queries instead to find all the records set as 'Primary' and change them to 'Inactive', and then to set the current record to 'Primary'. It will have to do for now Thanks anyway!