Wine Database - One bottle to one bin question

[RFJ]

I've got a wine rack that stores 6 bottles on every row and there are ten
rows.

I want to be able to allocate a bottle to say Row3 position 5 - so I need to
check that there isn't a bottle in R3/P5 already.

I'm starting from scratch (I could never solve this problem in my first
database so could have nil, one or more bottles allocated to the same R/P
and no way of reporting >1.)

So I'm looking for a reasonably simple solution - as my ability level is
beyond basic not a lot more <G>

Any thoughts or suggestions would be appreciated.

TIA

Rob

 

[John Spencer]

IF all you are doing is this then a multi-field key field should solve the
problem

WineRackTable (with three fields)
RowNumber
ColumnNumber
WineBottle

In design view, select RowNumber and ColumnNumber fields and then select
Edit: Primary Key from the menu. This will allow you to only have unique
combinations of row and column in the table. If you attempt to add another
record to this table that has the same row and column combination, you will
get an error message,

Hope this gets you started.

 

[RFJ]

Excellent

Is there a way I can trap that error message and redescribe it to something
more meaningful eg "Full" <G> - or would it require a different approach.

TIA

 

[John Spencer]

Are you using a form for input? If so, you should be able to trap the error
in the form's error event.

I'm not sure of the error number, but if you deliberately enter a duplicate
in the form, you can get the error number from the form event.
SAMPLE Code follows

Private Sub Form_Error(DataErr As Integer, Response As Integer)

'Use this to discover the error number and then comment it out
MsgBox DataErr,, "This is the error number you need"

'Add this after you know the error number
IF DataErr = << The number you've found>> Then
MsgBox "Bin in use"
Response = acDataErrContinue
Else
response = acDataErrDisplay
end if

End Sub

 

[RFJ]

Right second time which is good for me <BG>

Tx for the help - much appreciated