Re: ComboBox Problem... Please, help me out!

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.

Martin wrote:
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?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200909/1

.

Relevant Pages

  • Re: ComboBox Problem... Please, help me out!
    ... One way may be to group the report on Transaction, ... particular account (that is, a listing of Accounts, with the Transactions to ... based on tblTransAcct. ... The Max's were intended to get the last sheet assigned to each ...
    (microsoft.public.access.formscoding)
  • Re: New Method for Authenticated Public Key Exchange without Digital Certificates
    ... have the account disabled. ... then the next time you do a financial transaction ... ... eventually arrives at your financial institution ... ... in order to do credit card transactions, the merchant has to ...
    (sci.crypt)
  • Re: ComboBox Problem... Please, help me out!
    ... I just deleted the subform and started over, ... The Max's were intended to get the last sheet assigned to each ... instance, sales, almost for sure, is an account that will be involved in many ... had to make sure that I was going to store the transaction in the last sheet ...
    (microsoft.public.access.formscoding)
  • Re: ComboBox Problem... Please, help me out!
    ... Is "sheet" analogous to a record in the ... "Once the account is selected and the user moves to the next ... An accounting book has many acc. ... I created a single form for writing down transactions based on Transaction ...
    (microsoft.public.access.formscoding)
  • Re: Query for Subform - G/L Account Transactions (Access XP)
    ... GLID COUNTER NOT NULL, -- a Counter is an AutoNumber ... xact_date DATETIME NOT NULL, -- transaction date ... Allow the user to select the description, reference, and transaction ... account in my chart of accounts. ...
    (microsoft.public.access.queries)