Data entry and list of existing records on same form

[speckytwat]

I have a simple form that adds a new record into a table called Transactions, which has such fields as Name, Transaction Number etc. and a button whose event adds the new record.

All well and good but I want the form to be a bit more clever than that:



1. Some of the fields should have fixed / limited values, for example
Transaction Type should be limited to DD, CHQ+ and several other values- so users can't add other (incorrect) data into the field. Obviously a Select / dropdown is ideal for this but I can't see how to convert the input (which the form adds by default) to a Select box.

2. Name should work as follows: If ContactType is set to Customer, it checks that the same Name is in the Customers table. If ContactType is set to Supplier, it checks that the same Name is in the Suppliers table- and so on.

3. Transaction Numbers should be unique- they're not involved in any
calculations so I did as advised by the documentation and imported them as text fields- but ideally the new Transaction Number (for the new record) should be (The highest existing one) plus 1, and this should autofill into the field when data is entered, so that the user doesn't have to look up existing Transaction Numbers and work out what the next one should be.

4. Lastly, the form also needs to display a "window" to the table of existing Transactions, with a vertical scrollbar, allowing users to see all the current transactions (e.g to make sure the one they're entering hasn't already been entered) and also sort them by whichever field they want for ease of reference.

Anyone know how these items can each be achieved? Sure, I know they're not show stoppers, but I want the users to have the easiest time possible when entering or editing data -> less support time!

[kennejd]

1. Some of the fields should have fixed / limited values, for example
Transaction Type should be limited to DD, CHQ+ and several other values- so users can't add other (incorrect) data into the field. Obviously a Select / dropdown is ideal for this but I can't see how to convert the input (which the form adds by default) to a Select box. I would add a combo box to your form and set it's control source to Transaction Type. Also limit list should be set to 'Yes'.

2. Name should work as follows: If ContactType is set to Customer, it checks that the same Name is in the Customers table. If ContactType is set to Supplier, it checks that the same Name is in the Suppliers table- and so on.
You'll need to add some conditional code on the after update event of the Contact box.

3. Transaction Numbers should be unique- they're not involved in any
calculations so I did as advised by the documentation and imported them as text fields- but ideally the new Transaction Number (for the new record) should be (The highest existing one) plus 1, and this should autofill into the field when data is entered, so that the user doesn't have to look up existing Transaction Numbers and work out what the next one should be.
Personally, I would set the Transaction Number to an autoNumber type. It will add the next number automatically.

4. Lastly, the form also needs to display a "window" to the table of existing Transactions, with a vertical scrollbar, allowing users to see all the current transactions (e.g to make sure the one they're entering hasn't already been entered) and also sort them by whichever field they want for ease of reference.
Access 2007 has a Split Form type....you might try that and see if it will work for you.

[speckytwat]

Thanks for these- I'll see how far I can get...

(Hmm... combo box sort of works, only problem is it's put multiple instances of each value that I want in the dropdown, e.g 14 of one, 22 of another and so on- obviously I only want a single copy of each allowable value for the user to easily select) ???)

[kennejd]

For your combobox issue with multiple entries....you should probably have a table that stores the values you want the users to use. Otherwise you need to change the recordsource of the combobox to include 'distinct' values....or group by.

[speckytwat]

Fixed that by remakijng the combo box but with distinct values as that seemed the only way. Onto the others...

DAMN - spoke to soon, the box now for some reason changes the values for ALL the records even when I'm trying to change the type for just one record! Any idea why it would be doing this??

Fixed (I think), needed to point it to "Type" field again.

[speckytwat]

Ok, well I've tried changing the data type for TransactioNo to AutoNumber, unfortunately when I do that I get this message:

"Once you enter data in a table, you can't change the data type of any field to AutoNumber, even if you haven't yet added data to that field"

!!!

What am I to do with this field? The numbers start from around 200000 upwards, the last one is let's say 201268, I need to have them auto-increment and appear in the "Add New Transaction" form somehow... but Access won't let me...

Created a new copy of the old table, gave the new table a new name, imported structure only (so I could go and edit the data type of the Transaction Number)... but I got this message when I tried to convert it to AutoNumber:

"Database allows only one AutoNumber field per table. Use the Number data type for similar fields"

Presumably this is because of the primary key (ID) which is already in the structure.

Any way around this? I need the Transaction numbers to start from around 200000...

[speckytwat]

I'm now using the following code behind the text box for Transaction Number, but I still can't get it to work-

Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim strTransactionNo As String
    strTransactionNo = Nz(DMax("TransactionNo", "AllTransactions"), 0) + 1
    Me!TransactionNo = strTransactionNo
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim strTransactionNo As String
     
   If Me.NewRecord Then
       strTransactionNo = DMax("TransactionNo", "AllTransactions") + 1
       Me!TransactionNo = strTransNo
   End If
   
End Sub

Private Sub TransactionNo_BeforeUpdate(Cancel As Integer)
End Sub

It appears whilst the data for the new record is being inserted in the form, but then the record gets added without the Transaction Number showing! (It just shows blank in the table)

Any ideas?