Not in list event not working on filtered combo box

[joycey]

Hi guys,

I have the following 3 tables & associated keys...

ORDER -> ORDERCARDS -> CARDS
order_id.....order_id...........card_no
client_id......card_no

I have a main form based on ORDER and the subform being the ORDERCARDS, which has a combo box to either select an existing card_no or enter a new one (sourced from table CARDS) - so far straight forward!

The problem I have is that a a card can only be associated with 1 person (i.e. client_id in ORDER), so I have the following filter attached to the combo box so that it only shows card_no's associated with the client_id on the ORDER form...

SELECT CARDS.CARD_NO
FROM ORDER INNER JOIN (CARDS INNER JOIN ORDERCARDS ON CARDS.CARD_NO = ORDERCARDS.CARD_NO) ON ORDER.ORDER_REF = ORDERCARDS.ORDER_REF
WHERE (((ORDER.CLIENT_ID)=[Forms]![ORDER]![ClientIDcombo]));

This does work (as it only shows previous cards issued to that same client), but when trying to enter a new card number the not_in_list event enters the card number in to the CARDS table but I receive an error message "The text you entered isn't an item in the list". The code I have used is...

Private Sub CARD_NO_NotInList(NewData As String, Response As Integer)
Dim strTmp As String

'Get confirmation that this is not just a spelling error.
strTmp = "Add '" & NewData & "' as a new Travelex Card?"
If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then

'Append the NewData as a record in the Cards table.
strTmp = "INSERT INTO CARDS ( CARD_NO ) " & _
"SELECT """ & NewData & """ AS CARDS;"
DBEngine(0)(0).Execute strTmp, dbFailOnError

'Notify Access about the new record, so it requeries the combo.
Response = acDataErrAdded
End If
End Sub

If I remove the filter on the combobox it works perfectly, but I can then select a card_no for any client_id (which I wish to avoid).

I know theres a conflict between the filter & the VB code (the card_no is being entered in to the CARDS table, but an entry isnt being created in the ORDERCARDS table, so I can sort-of understand why the information isn't actually in the list as the filter is filtering it out).

Any help would be really appreciated as this is doing my head in.

Cheers

Paul

 

[LPurvis]

Hi

You insert a new row into the Cards table:
>> strTmp = "INSERT INTO CARDS ( CARD_NO ) SELECT """ & NewData & """"

But, as you said, you have criteria upon the combo source to limit the results.
FROM ORDER INNER JOIN (CARDS INNER JOIN ORDERCARDS ON CARDS.CARD_NO = ORDERCARDS.CARD_NO) ON ORDER.ORDER_REF = ORDERCARDS.ORDER_REF
WHERE (((ORDER.CLIENT_ID)=[Forms]![ORDER]![ClientIDcombo]));

You seem to need a linking row in the ORDERCARDS table.
Something like:
strTmp = "INSERT INTO CARDS ( CARD_NO ) SELECT """ & NewData & """ AS CARDS"
CurrentDb.Execute strTmp
strTmp = "INSERT INTO ORDERCARDS ( CARD_NO, ORDER_REF) SELECT """ & NewData & """ , """ & Me.ORDER_REF & """"
CurrentDb.Execute strTmp

Cheers

 

[joycey]

Thanks for the reply, thats been really helpful although I'm not there yet.

It sort of works... after creating an entry on the ORDERCARDS subform, i receive a "Duplicate Primary Key entery error message" (I won't type that out in full) forcing me to exit the mainform (where I then receive the "Record cant be saved error message" on closing) but when I load the mainform back up, the info & record is there.

The not-in-list event is now...


Private Sub CARD_NO_NotInList(NewData As String, Response As Integer)
Dim strTmp As String

'Get confirmation that this is not just a spelling error.
strTmp = "Add '" & NewData & "' as a new Travelex Card?"
If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then

'Append the NewData as a record in the Categories table.
strTmp = "INSERT INTO CARDS ( CARD_NO ) " & _
"SELECT """ & NewData & """ AS CARDS;"
DBEngine(0)(0).Execute strTmp, dbFailOnError

strTmp = "INSERT INTO ORDERCARDS ( CARD_NO, ORDER_REF) SELECT """ & NewData & """ , """ & Me.ORDER_REF & """"
CurrentDb.Execute strTmp

'Notify Access about the new record, so it requeries the combo.
Response = acDataErrAdded
End If
End Sub

Any thoughts?

Cheers

Paul

 

[LPurvis]

Well... to be honest, something troubled me about your question.
You said:
>> a card can only be associated with 1 person (i.e. client_id in ORDER),

So each of your cards can be associated with one one Client.
Does that mean that a client can have many cards, but they all belong to only one client? But a card may be associated to many orders - as long as all are for the same client?
So in a process, is a card related to an order, but once related it can only be related to orders for that same client?

Your business rules would determine whether having a foreign key in the Card table for the ClientID would passable from a normalisation standpoint.
(Some would argue that the presence of the ClientID in the Order table means that inference of client ownership can be made indirectly. However that assumes that an order associated can be made first. And that a card isn't created, intended for a specific client, even if it doesn't yet have any orders to be related to.)

It's this necessity for an order association (through the ORDERCARDS table) between Cards, Orders and Clients that's causing this.
But, from a business standpoint, the random creation of a new ORDERCARDS record to associate a new card with a client might not make sense?

It would seem that the problem is that you're entering the new card record into an ORDERCARDS record. But your creating an all too similar joining record in the subform! (i.e. with the same unique index for the card?)
So it seems a card can be associated to only one client - and only one order. (Or at least, only one order once. Which would seem more likely - otherwise, why even have records available for selection if they can only be assigned to a single order - they're already assigned to one! :-s)

You could always coerce this by abanding the subform record creation in favour of the record you've just created in code.
Or force the subform's combo source, such that it displays the client's related cards... and the newly created card record (as yet unassigned).
Indeed, is there not an argument to be made for showing the cards assigned to that client - and any, as yet, unassigned cards. (As they need to be assigned at some point, and are unassigned until they are?)

 

[joycey]

Hi Leigh,

As you can probably guess I'm a bit of a newbie to this world and these 3 tables are part of a much larger db. I was toying around with having the cards linked directly to the client (client table) but my issue was...

A client can make many orders(sales)
An order can have multiple cards (new or existing)
A card may be therefore associated with many orders but only 1 client

But thankyou as you are correct by associating the client id within the cards table, by default each order will be linked to that client . School boy error and as the kids say today... My bad.

Thank you, it's really appreciated

Paul

 

[LPurvis]

So it is reasonable that you can associate a card with a client, without them requiring an order first?
If so, then yes - there's no issue about it. The apparent circular reference isn't that - as the business rules dictate that a client must be associable to a card without the need of an order. Therefore the tables reference each other.

Glad you're sorted.

 

[joycey]

Hi Leigh,

I have changed the tables and relationships but I'm now have another dilema. My tables now look like this...

CLIENT --> CARDS --> ORDERCARDS -> ORDER
client_id.....card_no.....cardlist_id.........order_ref
...................client_id......card_no
......................................order_ref

The problem that I now have is that as many cards can be linked to 1 order, effectively multiple clients can now be associated with 1 order (which I think is the reason that I popped the client_id within the order table initially).

If you could please advise i would be very grateful (I assume theres a method by introducing another table but really cant get my head round it).

Thanks

Paul

 

[LPurvis]

Are you only associating clients with orders through the OrderCards table?
i.e. you no longer have a ClientID FK in the Orders table?
Again that seems escessive (as it requires the existence of OrderCard records as an order is created).
If it the ClientID FK still present, then that is your client order association.

I dislike multifield keys and will avoid them whenever possible.
If you want to enforce the consistency of a single client at the database level (as opposed to application) then you could do so.
You could introduce the key to the OrderCards table to be OrderRef and ClientID.
(And RI that with Orders.)

Personally, I'd build a schema which follows the business rules. And then not chase my tail trying to enforce all such rules through the database. I'd offer cards that only were associated with the order's client.
Even when more comples constraints are possible (in, say, SQL Server) I'd still be making the application the first line of defense against this.

 

[joycey]

Hi Leigh,

I forgot to thank you but your advice worked, and what I was trying to achieve wasn't feasible. I have now created it so that on allocating a client to an order,I allocate a card to them and then select which card the order is for based on cards allocated to that client. So... thank you.

Whilst I'm here and you may be able to advise... I have another table linked to orders for receipting purposes, which works fine (ie I am able to allocate a number of different payments/values to the one order) - but what I cant seem to work out is how to record my banking. Any suggestions? So far, it looks like this...

ORDER ------> TILL
order_ref......receipt_id
......................order_ref
......................amout_paid
......................date
......................type

The above is quite self-explanatory although "Type" is a list value with the likes of "Credit Card", "Cash" etc that the end-user needs to select. The only issue (evident to me anyway) is that theres nothing to stop someone from receipting more than the order value (although I suspect that would be designed at program level).

Any ideas how i would expand this to show a banked amount? I wasnt sure if it would be within the same table but with -(minus) values or if it would be through another table. Also, as there are multiple receipting options and with "Cheques" and "Cash" being the only 2 that are physically paid in on the same transaction, how to differentiate between the different banking methods.

I would be very grateful, if you could just point me in the right direction (rather than waste too much of your time) as I've reached a stage that is unfortunately beyond my current abilities (I would love to learn it properly from scratch but time and a demanding family prevent me from soing so).

Thanks again

Paul

 

[LPurvis]

Hi

I'm not entirely sure what to suggest, as I can't be sure what your problems are.
If you want to know how much has been banked... then is that not as simple as a field on the receipt indicating if that amount has been banked?
(A boolean field even.)
Or, in the case of cash, if part of it might have been banked, then some field indicating the amount of which has been banked.
Yes, you could have another table for deposits, and refer to the receipt record that funded that. But it seems slightly unecessary based on what I've gleaned from the above.

Cheers

 

[joycey]

Again...my brain is trying to over complicate unnecessarily.

Thanks

Paul