Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2011
    Posts
    11

    Unanswered: Data entry and list of existing records on same form

    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 fields- 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!

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    1. You can convert the text box holding the trx type into a combo box. The row source of the combo box can either be hard-coded with the values you need, which is quick and dirty but not easy to maintain, or the result of a SELECT statement from another table, which is easier to maintain and much cleaner. A combo box holds values in much the same way as a text box, so integrating this into your button's code shouldn't be onerous.
    2. You need to code the name field's BeforeExit event to conditionally check either the Customer or Supplier tables, and fail if the entered value isn't there. Of course, if you're feeling really flash, you can convert it to another combo box, and use the AfterExit event on the contract type control to change its row source to look at the relevant table.
    3. The trx number can be generated when the record is saved. Add some code to the start of the button that does the saving, so that it looks up the largest existing number, converts it to numeric data, increments it and converts it back to text before adding it to the form and using it in the record. The reason I'd generate the number at this point is that if you have multiple users and the number is generated when you start entering a new record, you're running the risk of frequent duplications.
    4. Create a query based on the trx table, showing what you want the users to be able to check. Use this query as the basis for a subform on your main form. To the end of the code that saves the record, requery the subform.

    The above is just how I would do things - if you wait for another nine suggestions, you'll have 15 or 20 ideas to play with!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Mar 2011
    Posts
    11
    Thanks, regarding #4 - what would I put in this query exactly?

    Also #3 - that sounds really complicated, I can't see a way of doing that in Access, changing the data type and then changing it back again?

    And (sorry, working backwards here) #2, what code would I put in the BeforeExit? I guess some sort of "IfEXists" statement but not sure what the syntax would be...

    Thanks!

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by speckytwat View Post
    Also #3 - that sounds really complicated, I can't see a way of doing that in Access, changing the data type and then changing it back again?
    Not complicated at all!

    Where StringControl is a Text field

    Val(Me.StringControl) + 1

    will covert it to a Number and add 1 to it

    You can then use the Str() function to convert it back to Text. So

    Str(Val(Me.StringControl) + 1)

    takes the Text control StringControl, converts it to a Number, adds 1 to it, then converts it back to a Text again.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Point 4 - you put in the query the fields from the transaction table that you want the users to use to make sure that they are not about to duplicate a transaction. Beyond that, without knowing the layout of the transaction table, I can't really advise.

    Point 3 - what Missinglinq said, except that for Me.StringControl, I'd use something like DMax to find the highest value transaction number in use.

    Point 2 - you need something like a DLookup to validate the entry in the control against the relevant table. BeforeExit events contain a Cancel variable, and in order to prevent the control from relinquishing focus, you set this variable to True. Review the help file for DLookup and BeforeExit - it's really useful (sometimes).
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Mar 2011
    Posts
    11
    Thanks for the replies, I'm just trying to figure where I would place the suggested code- do I need to create a new field called StringControl? Where would I place the code for this field? (not sure where this would be placed? In one of the Properties for the field?)

    Basically I have the input form for "Add a new transaction" ready, and I want the field for Transaction Number to be pre-filled with (the highest existing transaction number plus one)...

    I've loooked through each of the Properties for the input field but I'm not sure where the code would be placed and which properties would need to be edited to make it work this way?

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You could always post what you've got and let people have a look. A lot of the time, it's easier to show with worked examples.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  8. #8
    Join Date
    Mar 2011
    Posts
    11
    Thanks, not sure what I can post really, it's just an input box on a form, I don't have any code for it as I can't see where I'm supposed to add the code. I've looked through the Properties for the input box but can't see where I need to change things in order to get it to show the right value (highest Transaction Number plus 1) by default...

    I'll try attaching a screenshot of the input- not sure how much use it will be though!
    Attached Thumbnails Attached Thumbnails screen.jpg  

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    What I mean is, you can zip the database up and post the zip file. (Having said that, the screenshots look like a later version of Access than I have, so my continued input is likely to be minimal.)
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •