Limiting Entries

[brettvba]

How would I go about limiting the entries that are put into my database.

There are many fields but I only want to base it on 2 of the fields

One is a supply number and the other is a type
eg 11111 and Frozen

There can be many of either of the two fields but only 1 record with the both
eg only 1 record containing 11111 and Frozen.

Any Ideas?

Brett

 

[Corticus]

Hi brettvba,

Are you saying you want to retrieve records that have data in both fields supply number and type?

You could limit data entry to a query that has the criteria for these two fields set to Not Is Null.

If you are looking for a record that contains specifically those values, make a query and set the criteria for supply number to 11111 and type to "Frozen".

I might be totally missing the point, if so, please post back...

 

[brettvba]

Hi Corticus I'm trying to limit the entry not the retrieval so that I don;t have duplicate entries in the database.

Brett

 

[Corticus]

Set both 'supply number' and 'type' as your primary key in this table. This will forbid the entry of a record with the same values for 'supply number' and 'type' as another record.

Just select both fields at once with the table in design view, and set the key.

HTH,

 

[Tanis]

Put this code in the Before Update event of the second control. i.e. Type control. I am assuming that Supply Number is the first control and that Type is the second control.

Private Sub Type_BeforeUpdate(Cancel As Integer)
If DCount("[Type]", "tblYourTableName", "[Type]= '" & Me![Type] & "'") = 1 And DCount("[SupplyNumber]", "tblYourTableName", "[SupplyNumber]= '" & Me![SupplyNumber] & "'") = 1 Then
MsgBox "Duplicate record"
Me.Undo
Cancel = True
End If
End Sub


David

 

[brettvba]

Just wondering here but how do you set two fields as primary keys?
I have my ID as a primary key at the moment?

Brett

 

[Corticus]

Hi Brettvba,

If I gather correctly, this table should never have two records with the same values for these two fields. If this is the case, you could keep the ID field, which you might need to reference later, I'm sure, but don't make it the key.

Open the table in design view, take the key off the ID field, and select the rows that contain the fields 'type' and 'supply number',like you would select two rows in Excel, and then hit the key icon. Since you should never have two records with the same values in both these fields, you could say that the two fields together serve as a unique identifier for that record, and Access will not let you add duplicates.
You have to remove ID as part of the key, becuase ID will be different for every record, even if the other fields have a duplicate. The idea of a multiple field key would to force the combination of the fields to be unique, but would allow duplicate values in one of the fields.

---/key/key
ID/Type/supply number

would reject the bold record:

1/A/100
2/A/200
3/A/300
4/B/100
5/B/200
6/C/100
7/A/200

HTH,

 

[brettvba]

Ok Cool, Thanks Corticus I am still teaching myself Access that was something I didn't know!

Thanks

Brett

 

[Corticus]

Glad I could change your to a !