Results 1 to 10 of 10
  1. #1
    techfixes is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    4

    Lightbulb Lookup Text Boxes from a Table, Access 2010

    I'm having a lot of trouble working out a solution to this [Test Copy of Database Attached]
    Views: 6 Size: 72.3 KB">TestDB3.zip


    The idea is fairly simple. When the user enters or selects a (valid) Customer code, the next Invoice Number in the sequence is automatically filled in, as well as today's Date. After that, 1 of 2 things will happen:

    1. To create a new Invoice the user presses the Enter key (after optionally filling in the Order Number).

    2. The user can Edit the Invoice Number Text Box and it will check for an Invoice with an exact match for Invoice Number / Customer Code. If one is found, it will auto-fill the Date Text Box with the Invoice Date and the User may then press Enter to confirm editing of that Invoice in frmINVOICEENTRY.

    However I'm not quite sure how to achieve this - particularly step 2. Should I use the DCount method, or something entirely different?

    I'd also like to know how to resize that [censored] SubForm in frmINVOICEENTRY


    A test copy of my Database is attached. Click the Invoices button to see it in action. Also a cleaned up and commented example of the Code I am currently using is below (this part is working fine):




    Code:
    Private Sub Text1_AfterUpdate()
        If DCount("CUSTCODE", "tblCUSTMAST", "[CUSTCODE] = '" & Me.Text1.Text & "'") = 0 Then       ' Check Contacts Table for valid CUSTCODE
               DoCmd.OpenForm "frmCUSTMASTLIST", WindowMode:=acDialog, OpenArgs:="InvoiceMode"      ' If not found, open Customer List Form to select. This will return a value to the CUSTCODE TextBox
        End If 
      [Forms]![frmINVOICESELECTION]![Text2] = DMax("[INV_NUM]", "tblINVHDR") + 1                    ' Auto-Fill 2nd TextBox with highest Invoice Number +1  (more sanity checking will be added later)
           If Len([Forms]![frmINVOICESELECTION]![Text3] & vbNullString) = 0 Then                    ' Check if a Date TextBox is null (ie. has not been manually entered or previously filled in)
                   [Forms]![frmINVOICESELECTION]![Text3] = DATE                                     ' If not, insert today's Date in 3rd TextBox
           End If
      [Forms]![frmINVOICESELECTION]![Text2].SetFocus                                                ' Set focus back to Invoice Number form for further editing if necessary
    End Sub


  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why don't you use comboboxes to aid in selection of existing records and entering data into new record?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    techfixes is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    4
    Quote Originally Posted by June7 View Post
    Why don't you use comboboxes to aid in selection of existing records and entering data into new record?
    I'm re-creating an old DOS program so I kind of need to stick to the script

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I had to build Access database to migrate from old DOS dBaseIV program. Didn't let it hold me back from using the features available in Access. A combobox provides the advantages offered by both a textbox and a listbox. Users can still type into the box but the dropdown list aids in entering valid values. If value entered is not in the list, that can be handled. The forms can still have the same 'look and feel' just better.
    Last edited by June7; 04-24-2017 at 10:52 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I agree with June regarding the use of combo boxes.
    Having looked at your db I would question whether is properly normalized. You have a number of fields in a couple of tables which use numbers in their names e.g. Q1, Q2, Q3 etc
    I would recommend making sure it is normalized before developing forms etc
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Agree with Bob about the data structure. Meant to comment on it earlier. If you need to sum (or any other aggregate calc) those 20 fields, you will find it quite cumbersome to accomplish. In a normalized db those 20 fields would be separate records in a dependent (child) table associated with the parent tblINVDET. Having said that, IMHO it is a balancing act between normalization and ease of data entry/output. I do have a db that has a several sets of 'replicated' fields and this suits our needs just fine because don't have to do aggregate calcs with those fields and it makes for simpler structure and data entry/output easier. However, biggest replicated set I have is 6 fields and for most sets all fields have data. It seems you will have a lot of empty cells and will 20 always be enough?

    I do have an exception to the ease of data output. I have to do graphing with some of those field sets and this means I have to use UNION query to rearrange the data to normalized structure.

    Normalize until it hurts, denormalize until it works. http://www.agiledata.org/essays/dataNormalization.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    techfixes is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    4
    I sort of understand the arguments about Combo Boxes, however they aren't really necessary in this context. If you test out the Demo, you will see that if a valid Customer Code is not typed in right away, a selection form pops up and makes it easy to enter that field. The next 2 boxes on the Invoice Selection form (the red form) are filled in automatically from there. It's only if the user wishes to change those that anything needs to be typed in

    So we have data in 3 boxes... My problem is how to validate that Data and go onto the next steps. More specifically it is Data-Matching the first 2 fields (CUSTCODE & INVNUM). Easy to do in SQL, I just don't know exactly how to do it in this context.

    Once the Input has been confirmed I will pass it on to the frmINVOICEENTRY to create or edit an Invoice.

    Also, does anyone have any tips about how to fix the size of that SubForm in frmINVOICEENTRY ?

    (I will take on board the comments about Data Normalization etc. Thanks, I appreciate it)

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Be aware that "Type" (in tblCUSTMAST) and "Date" (in tblINVDET and tblINVHDR) are reserved words and shouldn't be used as object names> Plus, they are not very descriptive.... "Type" of what??? "Date" of what????

    In the code, you have lines like
    Code:
            If DCount("INV_NUM", "tblINVHDR", "nz([INV_NUM]) = '" & [Forms]![frmINVOICESELECTION]![Text12] & "'") <> 0 Then
                MsgBox "INVNUM: YES"
            Else: MsgBox "INVNUM: NO"
            End If
    The colon (after ELSE) was used in IBM Basic as a command separator (Memory was VERY expensive - this helped conserve memory useage). Its use has been depreciated. It still works - backward compatibility - but you should NOT use the colon in code nowadays.

    Better is
    Code:
            If DCount("INV_NUM", "tblINVHDR", "nz([INV_NUM]) = '"  & [Forms]![frmINVOICESELECTION]![Text12] & "'") <> 0 Then
                MsgBox "INVNUM: YES"
            Else
                MsgBox "INVNUM: NO"
            End If
    You could use
    Code:
    Private Sub Form_Load()
        If Not IsNull(Me.OpenArgs) Then
            DoCmd.Close acForm, "frmCUSTMASTLIST", acSaveNo: Me.Text2 = Me.OpenArgs: Me!Text12.SetFocus
        End If
    End Sub
    but how hard it that to read/edit????


    You have this line in several places:
    Code:
            DoCmd.Close acForm, "frmINVOICESELECTION", acSaveNo
    The argument "acSaveNo" is for saving DESIGN changes, not data entries. So, unless your code is opening the form in design view, "acSaveNo" has no effect - the data will still be saved.


    Also, in EVERY code module, the first two lines should be
    Code:
    Option Compare Database
    Option Explicit
    In the IDE, there is a check box, "Require Variable Declaration", that should be checked (TOOLS/OPTIONS/Editor tab)
    After the check box is checked, in any NEW module, "Option Explicit" will be added automatically.


    I'd also like to know how to resize that [censored] SubForm in frmINVOICEENTRY
    In both "sfrmINVOICEENTRY2" and the subform control in "frmINVOICEENTRY", the form footer height is 0, but the form footer section is about 3 inches long.
    Move the bottom of the form (where the scroll bar is) up to the bottom of the detail section (where it says "Form Footer" and save the form.
    Ta-Da!!!

  9. #9
    techfixes is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    4
    Thanks so much ssanfu ! A lot of good tips there. I will save them and work through them. I still can't manage to resize the Form Footer. It just doesn't work in the usual way.

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Strange... I didn't have any problems re-sizing the form...
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 01-31-2013, 02:58 PM
  2. Replies: 3
    Last Post: 01-31-2013, 01:03 PM
  3. Access 2010 Lookup Column Display
    By cartotech81 in forum Queries
    Replies: 2
    Last Post: 09-19-2012, 07:23 PM
  4. Replies: 2
    Last Post: 08-09-2012, 04:15 AM
  5. Replies: 3
    Last Post: 01-17-2012, 01:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums