Partially Fill Key Fields

[RexesOperator]

I have a mainform Transactions with TransactionsID and a subform Site Details with SiteID. For non-database related issues (my users like things the way they are) at the moment I have to use text strings for the key fields instead of autonumbers.

SiteId begins with the TransactionID number (the program does check for uniqueness for TransactionID). At the moment the user copies and pastes the TransactionID into the SiteID textbox, and then just needs to add the site name.

I would like the SiteID to have the TransactionID partly filled in, so that one more step is eliminated. I have tried using forms!fTransactions!TransactionID, but I get #Error. I have also tried copying the TransactionsId text box onto the Site Details form and setting the property to invisible, and then using the forms! and I still get #Error. My first attempt was to change the data control of SiteID to TransactionID, but that didn't work either - it changed all the SiteIDs to their respective TransactionIDs. Fortunately I was working with a copy (rule number one of database design) and no harm done.

Is the issue that I am dealing with is that SiteID is a key field and that key fields can't be calculated? If so I will long for the day when all my key fields can be switched to autonumbers.

 

[gemma-the-husky]

at what point are you trying to assign the transactionid INTO the siteid box, and how are yuo doing it. If you can paste it, you can do it with code, but what are yuo trying to do, to accomplish this.

When yuo say you have a form and subform, normally there would be al ink between the two. In this case it sounds as if this is not the case, yet you ARE pasting one from the other. Can you clarify the link between your form and subform.

 

[RexesOperator]

Sorry I wasn't clearer.

Yes there is a link between the two - TransactionsID is the key field in the Transactions table and links to TransactionsID in the Site table. I am trying to do this when adding a new transaction.

To give you an idea the TransactionID looks like CompanyNameContactNameDate. To generate the SiteID, the site name is added: CompanyNameContactNameDateSiteName. (Yes I know this is not good database design, but it will do until I can convince my users - actually only 2 users - otherwise.)

 

[gemma-the-husky]

I am struggling to understand this. You have presumably 1 site ID = several transaction ID, so how can the site form be a subform of the transactions form?

If you are entering a transaction directly, you just need a combo box to pick the site. On the other hand, if you do it the other way, and have the site as the main form, and the transactions as the subform, then you should be able to do what you are trying to do.

Are you saying that you actually have multiple Sites for each Transaction?

 

[RexesOperator]

That's correct - the relationship is one transaction with many sites, so one transaction will have many site ids. Thats why the Site Details is the subform of the main Transactions form.

 

[gemma-the-husky]

OK - The syntax seems right - let's try something else. Is the field on the transaction form called TransactionID, or is that the underlying data. The expression forms!fTransactions!TransactionID is looking for a field name?

 

[RexesOperator]

The field name is TransactionsID. I tried to attach a stripped version, but the upload failed. I can get the value from the main form (I must've had a typo) into the SiteID text box now, but I can't add any more characters to the SiteID field- it seems locked.

 

[gemma-the-husky]

If you are cutting and pasting with no problem, then presumably its something else.

Where are you putting your code to set the value of the siteid to be transactionid from the main form? In a button, or in a form event?

 

[RexesOperator]

I will have to follow up on this tomorrow. The code is actually in the Control Source of SiteId. I will try and upload a stripped version to show you tomorrow. Happy Eastern Standard Time (for me anyway).

 

[RexesOperator]

My internet provider at home decided to die on me and I can't upload from work (don't know why - I've been able to before). However - the problem turned out to be a typo. Once I stripped the dbase down to the form, I found out where I had a 1 instead of !. Everything now works the way it is supposed to - I hate losing an argument to a machine . Thanks for the efforts to track down the problem.