Data Input Form

[AlexN]

Hi everyone, long time no see, trying to avoid bothering you all beautiful people with silly questions.

Well, I got a tblTransactions, that holds records of everyday transactions on multiple accounts, and bears fields like TransactionID (PK), TransactionDate, AccountID, TransactionType. (There are plenty of others, these are the most significant).
What I want to do, is to have a form for data input to this table, in a way that I don’t have to input AccountID, TransactionType, and TransactionDate, over and over again, as long as we’re talking for the same AccountID, the same TransactionType, and the same TransactionDate, for multiple transactions that bear the same values for these fields.
This should be a kind of MainForm-SubForm combination I suppose, and I’m sure I’ve done it in the past but I don’t remember how. I’m also pretty sure that somewhere in this forum there’s an answer for this but I’m too tired to search. Anyone kind enough to give some ideas?

Thanks

 

[Gasman]

I think you are talking about copying values from the previous record?

Search for that.

I use something along the lines of

Private Sub Date_ID_AfterUpdate()
    If Me.chkCopy Then
        Me![Date_ID].DefaultValue = """" & Me![Date_ID].Value & """"
    End If
End Sub

 

[AlexN]

I think you are talking about copying values from the previous record?

Search for that.

I use something along the lines of

Private Sub Date_ID_AfterUpdate()
    If Me.chkCopy Then
        Me![Date_ID].DefaultValue = """" & Me![Date_ID].Value & """"
    End If
End Sub

No it definitely isn't about copying values from previous record. It's about having controls with repeated values in the forms header (once), and all other values of the records in the detail section (or something like that).

Thanks for your answer though.

 

[jdraw]

This might be a case where, if you showed us (picture) what you currently have in your form and the result, and then show us what you really want, you could clarify the requirement.

"A picture is worth more than XXX words"

 

[stopher]

Create a continuous form with the fields that need to be entered into the table. In the header, create text boxes to the fields you want to repeat. Then for the BEFORE INSERT event for the detail section, use the following code

    Me.TransactionDate = Me.txtTransactionDate
    Me.TransactionType = Me.txtTransactionType
    Me.AccountID = Me.txtAccountID

Now each time you click in a new line, the data will be copied from what ever is in the header.

See attached example

 

[AlexN]

This might be a case where, if you showed us (picture) what you currently have in your form and the result, and then show us what you really want, you could clarify the requirement.

"A picture is worth more than XXX words"

Well that's the table structure more or less :

And that's the form I created :

I first created a form bound to tblTransactions using only the AccountID, TransactionType and TransactionDate fields, and then I created a subform bound to the same table, using all fields. Main form and subform are linked by the three main form controls. Looks workiing alright but....(coming soon)...

 

[stopher]

In your relationship view you should not relate tblTransactions to tblTransactionCategories. The two tables are already related via tblTransactionTypes. You also need to remove TransactionCategory from tblTransactions as this should be determined from the other relationships.

The reason is, with the three way link, you could have tblTransactions with a a given transaction type relating to one category but have the category as a completely unrelated category not relating to the transaction type (because your relationship does not prevent this).

 

[stopher]

In your relationship view you should not relate tblTransactions to tblTransactionCategories. The two tables are already related via tblTransactionTypes. You also need to remove TransactionCategory from tblTransactions as this should be determined from the other relationships.

The reason is, with the three way link, you could have tblTransactions with a a given transaction type relating to one category but have the category as a completely unrelated category not relating to the transaction type (because your relationship does not prevent this).

Looking at this I actually think you need category in the type table i.e. a category has many types. Is that right? At the moment you have one type has many categories.

 

[jdraw]

Alex,

I think Chris (stopher) has given a solution that meets your requirements.
Good luck.

OOoops:
I just saw the latest posts, and agree( post 7) that there is an issue with transactionCategory and transactionType.

Need clarification or examples of TransCategory and TransType.

eg (example only)

[I]Category[/I]
Leave -------> TransType  Vacation, Sick, Bereavement,....
Finance------>TransType   Credit, Debit, Grant, Loan, Payment....
Hobby -------> TransType   Swimming, Kayaking, Skiing, Jogging, Reading

 

[AlexN]

Looking at this I actually think you need category in the type table i.e. a category has many types. Is that right? At the moment you have one type has many categories.

It really goes this way:One type has many categories.

Types are :Withdrawal, Deposit, Transfer
Categories are like : Groceries, Insurance, Car Expenses, Salary, Bonuses etc.

 

[stopher]

It really goes this way:One type has many categories.

Types are :Withdrawal, Deposit, Transfer
Categories are like : Groceries, Insurance, Car Expenses, Salary, Bonuses etc.

Then I would suggest that type and category aren't really related directly. They are really related through transaction. It's the transaction that gives them some relationship.

The reason I question is because it is unusual to see a circular relationship like this.

 

[AlexN]

Then I would suggest that type and category aren't really related directly. They are really related through transaction. It's the transaction that gives them some relationship.

The reason I question is because it is unusual to see a circular relationship like this.

You’re absolutely right about circular relationships, I moved them away. However, I won’t remove TransactionCategory field form tbltransactions because there is no other field uniquely determining the Transaction category in this table, directly or indirectly.

Having solved the issue previously mentioned, I now come with another:
I have this MainForm that bears two subforms, subformA and subformB. These two subforms are synchronized via a couple of (hidden) txt controls in the MainForm: That is, txt controls take values from the record selected on subformA filtering subformB which displays related records. Problem is, when I want to add a new record in subformB (hitting a button with a GotoRecord command), nothing happens, subformB stays in related records displayed due to txtcontrols. Any ideas?
Hope I made myself clear

 

[jdraw]

I see transactionCategory and AccountCategory in your earlier post.

Not sure what AccountCategory is???
From this

Types are :Withdrawal, Deposit, Transfer
Categories are like : Groceries, Insurance, Car Expenses, Salary, Bonuses etc

.
I'm wondering if Category represents something like TransactionPurpose.

I understand Withdrawal and Deposit. Isn't transfer the equivalent of a Withdrawal and Deposit pair??

I'd like to clarify the tables and relationships before trying to solve using form/subforms etc.

Just some thoughts for consideration.

 

[AlexN]

I see transactionCategory and AccountCategory in your earlier post.

Not sure what AccountCategory is???
From this
.
I'm wondering if Category represents something like TransactionPurpose.

I understand Withdrawal and Deposit. Isn't transfer the equivalent of a Withdrawal and Deposit pair??

I'd like to clarify the tables and relationships before trying to solve using form/subforms etc.

Just some thoughts for consideration.

It's all a matter of terms, and about how I want to get results later on.
We Greeks, don't use the term account for groceries for instance, groceries are an expenses category for us.
Yes , TransactionCategory stands for Transaction Purpose , and, AccountCategory defines the kind of the account (savings, checking, cash, etc).
Transfer is a transaction that involves two accounts ('From' and 'To') and puts two records in the table by writing one line of data.

 

[jdraw]

I think your underlying data model (tables and relationships) should be more like the attached. (only some fields included [including some spelling mistakes]) The intent was to show a possible table/relationship diagram representing the posters situation.