Wine Database - One bottle to one bin question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RFJ - 19 Apr 2006 11:52 GMT

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

Reply to this Message

John Spencer - 19 Apr 2006 13:36 GMT

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.

> I've got a wine rack that stores 6 bottles on every row and there are ten
> rows.
[quoted text clipped - 14 lines]
>
> Rob

Reply to this Message

RFJ - 19 Apr 2006 15:17 GMT

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

> IF all you are doing is this then a multi-field key field should solve the
> problem
[quoted text clipped - 30 lines]
>>
>> Rob

Reply to this Message

John Spencer - 19 Apr 2006 20:15 GMT

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

> Excellent :)
>
[quoted text clipped - 38 lines]
>>>
>>> Rob

Reply to this Message

RFJ - 20 Apr 2006 17:38 GMT

Right second time which is good for me <BG>

Tx for the help - much appreciated

> Are you using a form for input? If so, you should be able to trap the
> error in the form's error event.
[quoted text clipped - 60 lines]
>>>>
>>>> Rob

Reply to this Message