ComboBox Problem... Please, help me out!

[Martin]

Thanks in advance… I’m new into this so my explanation may be larger than
usual!!!

I’m developing an accounting book application. Basically, it’s gonna record
accounting transactions for different accounting periods.

1. An accounting period may have many accounting books
2. An accounting book has many acc. sheets (Number specified when defining
the acc. book)
3. Each acc. sheet belongs to a single account but an account may have many
acc. sheets withing the same acc. Book (Depending on how many transactions
the account is involved over the period)
4. Each acc. sheet has many lines (Number specified when defining the acc.
book)

I created two tables to save transactions… “Transaction register†and
“Transaction Detailsâ€

I created a single form for writing down transactions based on Transaction
Register and a subform for Transaction Details which is in Datasheet View.

Here come the issues…

I have a Combo Box on the subform “Transaction Details†where the user is
going to select an account from. This Combo Box has 4 columns, the first one
is dependent, the others are used to fill out other controls once the user
has made his choice, except for the third one which is used to guide the user
without id’s (this is the only column seen by the user). The RowSource
property is a sql statement that returns sql integrated functions (Max)
because the application must look for the last sheet assigned to the account.
I have two problems:

1. Once the account is selected and the user moves to the next record, the
control should take the account picked out of the list… I tried with “SELECT
Max(IdSheet),Max(Var2), AccountName…, WHERE IdSheet NOT IN (SELECT IdSheet
FROM TransactionDetails WHERE IdTransaction=†& Me.txtIdTransaction & “ GROUP
BY IdAccount†But it doesn’t work. Any idea?

2. When the user moves to the next record, the control gets a blank given
that the information seen is not saved… I want the user to see the account
chosen… Any idea?

 

[Martin]

Thanks BruceM for your help! I answered all your question so that you can
give some advice...

Are books part of the structure, or are you describing the paper-based system
on which the project is based?

Both books (main form) and sheets (subform) are part of the structure on
this form, I’m also describing the system as for you to get the whole
picture. The main form gets Idbook, IdTransaction and other fields

Are there customers in a Customer table? There’s no customer table.

Is there an Accounts table? Yes, there’s an Accounts table

What is the basis for a sheet? Every sheet belongs to an only single
accounting book (they’re linked on idbook) An accounting book may have many
accounting sheets

Is "sheet" analogous to a record in the Transaction Register?

Yes, the Transaction Register saves the transactions for every active
account, and given that each accounting sheet is assigned to an only single
account, it’s possible to pick a sheet up by selecting an account (the system
is supposed to get the last sheet assigned to “that account selected by the
userâ€). The main objective of this application is to replicate a physical
accounting book, so any person may write transactions down just as it’s
printed on the transactions report over the period

When you say the first column in the combo box on the subform is dependent,
do you mean it is the bound column?
Yes, that’s exactly what I mean.

Is the combo box bound to a field that can accept the bound column value?
Yes, the bound column corresponds to idsheet and this is exactly the
ControlSource (idsheet)

Is there a relationship between the Register and Detail tables?
Yes, they’re linked on idtransaction field

If so, is the subform control linked on the joining field?
Yes, the joining field is “idtransactionâ€

If the Row Source is limited to the latest record, what is there for the
user to select?

These are the columns returned by Row Source property:
idsheet (not seen, ColumnWidth=0), idaccount (not seen, ColumnWidth=0),
accountname, sheetnumber (not seen, ColumnWidth=0)

1.idsheet, idaccount and sheetnumber are gotten from a SheetsRegister Table
2.accountname is gotten from Accounts Table. This is what the user choice
will be, and the system must be able to look for the right sheet.

SheetsRegister and Accounts Table are linked on idaccount

When the user moves to the next subform record, or the next main form record?
I mean the next subform record

Does the SQL work by itself (in a standalone query)?
Yes, it does. This is the query:

SELECT Max(sheetsregister.Idsheet) AS Idsheet, sheetsregister.Idaccount,
Max(Accounts.Accountname) AS Accountname, Max(sheetsregister.SheetNumber) AS
SheetNumber
FROM Accounts INNER JOIN sheetsregister ON Accounts.Idaccount =
sheetsregister.Idaccount
GROUP BY sheetsregister.Idaccount
HAVING (((Max(sheetsregister.Idsheet)) Not In (SELECT Idsheet FROM
transactiondetails WHERE Idtransaction=Me.txtIdtransaction.Value)));

I tested this query by setting “the control value†to a known value (1 in
this case), and it works.

In what way does it not work?
I want not to have the accounts already chosen available in the ComboBox
list for the next subform record. I mean, an account is not to be recorded
more than once for any single accounting transaction; but ComboBox List
retains all active accounts so the user might select the same account twice
and that’s exactly what I do not want.

 

[Martin]

BruceM, I found to get it fixed... I do really appreciate your help and
thanks a bunch... I just deleted the subform and started over, I almost did
the thing. I must have done something wrong (don't know yet) when I was
creating the subform because my reasoning on SQL statement was pretty much
yours. The Max's were intended to get the last sheet assigned to each
account. I just explain the business situation… There’s going to be some
accounts that will be involved in most of transactions regularly. For
instance, sales, almost for sure, is an account that will be involved in many
transactions. Mechanism of sheet assigning is that an account will be
assigned to a new accounting sheet as soon as its current one runs out of
available lines. Remember that this application wants to reproduce a physical
accounting book so what you see on screen must almost be a scan of real
sheets. According to this, an account may have many accounting sheets so I
had to make sure that I was going to store the transaction in the last sheet
assigned to the account and that’s why I used Max on SQL statement.

By the way, I created the index you suggested with the idaccount and
IdTransaction to assure that there won’t be accounts appearing more than once
in the same transaction. I took it as another data precaution, you were
right, I shouldn’t have missed that.

--
Martin Esteves

This is what I have so far. You have tables I will call tblTransReg
(transaction register) and tblTransDtl (transaction detail).

tblTransReg
TranscationID (primary key)
Other fields to identify the transaction

tblTransDtl
TransDtlID (PK)
TransactionID (link to tblTransReg)
AccountID
TransDate, etc.

You also have tables for SheetRegister and Account. I get the account table
(a lookup table, from what I can understand, for selecting an account for
tblTransDtl, but I don't see what SheetRegister does.

Part of the difficulty here is understanding the actual business situation.
For instance, what is the nature of a record in tblTransReg? Is it for a
customer, or for a contract, or for a purchase, or what? I'm not looking for
a lot of detail, but rather for a better understanding of the real-world
situation behind the database.

Also, a listing of the tables and fields, something like I have done, would
be helpful. I don't need to know every field, but only the ones relevant to
the questions at hand.

You say that the first column of the combo box (idsheet) is bound. Bound to
what field in tblTransDtl? Is there a relationship between tblTransDtl and
the SheetsRegister table? You say further that there is a relationship
between SheetsRegister and Accounts. To what end?

Regarding the combo box, are all Accounts available for every Transaction
Detail initially? I think I understand that after an Account has been
selected for a Detail record for a Transaction Register record, you don't
want the Account to be selected again for that Transaction. One rather
simple way to prevent that is to place a unique index on the combination of
idtransaction and the Account. The account will still show up on the list,
but it cannot be selected again since there can be only one instance of a
particular account for a particular transaction.

To limit the list to accounts not yet selected for that transaction your Row
Source could be something like this:

SELECT AccountID, AccountName
FROM Account
WHERE AccountID NOT IN
(SELECT AccountID
FROM tblTransDtl
WHERE TransactionID = Forms!frmTransaction!TransactionID)
ORDER BY AccountName

In the subform's Current event:
Me.ComboBoxName.Requery

I think TransactionID is named idtransaction in your project, and your main
form is probably named something other than frmTransaction, but I hope you
get the general idea. I don't understand the point of Max in your Row Source.


From what I can tell, Book is an arbitrary distinction. It may be all
transactions in a date range or something like that, but there is no need to
break transactions up in that way. All transactions should be stored in a
single table. If there is reason for books, that can be a field in the
transaction table.

Thanks BruceM for your help! I answered all your question so that you can
give some advice...

Are books part of the structure, or are you describing the paper-based system
on which the project is based?

Both books (main form) and sheets (subform) are part of the structure on
this form, I’m also describing the system as for you to get the whole
picture. The main form gets Idbook, IdTransaction and other fields

Are there customers in a Customer table? There’s no customer table.

Is there an Accounts table? Yes, there’s an Accounts table

What is the basis for a sheet? Every sheet belongs to an only single
accounting book (they’re linked on idbook) An accounting book may have many
accounting sheets

Is "sheet" analogous to a record in the Transaction Register?

Yes, the Transaction Register saves the transactions for every active
account, and given that each accounting sheet is assigned to an only single
account, it’s possible to pick a sheet up by selecting an account (the system
is supposed to get the last sheet assigned to “that account selected by the
userâ€). The main objective of this application is to replicate a physical
accounting book, so any person may write transactions down just as it’s
printed on the transactions report over the period

When you say the first column in the combo box on the subform is dependent,
do you mean it is the bound column?
Yes, that’s exactly what I mean.

Is the combo box bound to a field that can accept the bound column value?
Yes, the bound column corresponds to idsheet and this is exactly the
ControlSource (idsheet)

Is there a relationship between the Register and Detail tables?
Yes, they’re linked on idtransaction field

If so, is the subform control linked on the joining field?
Yes, the joining field is “idtransactionâ€

If the Row Source is limited to the latest record, what is there for the
user to select?

These are the columns returned by Row Source property:
idsheet (not seen, ColumnWidth=0), idaccount (not seen, ColumnWidth=0),
accountname, sheetnumber (not seen, ColumnWidth=0)

1.idsheet, idaccount and sheetnumber are gotten from a SheetsRegister Table
2.accountname is gotten from Accounts Table. This is what the user choice
will be, and the system must be able to look for the right sheet.

SheetsRegister and Accounts Table are linked on idaccount

When the user moves to the next subform record, or the next main form record?
I mean the next subform record

Does the SQL work by itself (in a standalone query)?
Yes, it does. This is the query:

SELECT Max(sheetsregister.Idsheet) AS Idsheet, sheetsregister.Idaccount,
Max(Accounts.Accountname) AS Accountname, Max(sheetsregister.SheetNumber) AS
SheetNumber
FROM Accounts INNER JOIN sheetsregister ON Accounts.Idaccount =
sheetsregister.Idaccount
GROUP BY sheetsregister.Idaccount
HAVING (((Max(sheetsregister.Idsheet)) Not In (SELECT Idsheet FROM
transactiondetails WHERE Idtransaction=Me.txtIdtransaction.Value)));

I tested this query by setting “the control value†to a known value (1 in
this case), and it works.

In what way does it not work?
I want not to have the accounts already chosen available in the ComboBox
list for the next subform record. I mean, an account is not to be recorded
more than once for any single accounting transaction; but ComboBox List
retains all active accounts so the user might select the same account twice
and that’s exactly what I do not want.

Are books part of the structure, or are you describing the paper-based system
on which the project is based? Same question for sheets.

[quoted text clipped - 61 lines]

that the information seen is not saved… I want the user to see the account
chosen… Any idea?