X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Options For Table Lookups / Resizing Of Subform, Access 2010    
post Apr 20 2017, 07:23 PM

Posts: 59
Joined: 9-November 16

[Test Copy of Database Attached]

I have decided on a different plan. Upon entering or selecting a (valid) Customer code, the next Invoice Number in the sequence will be 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 the exact Invoice Number / Customer Code - if found, 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 smile.gif

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)

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

Attached File(s)
Attached File  TestDB3.zip ( 72.27K )Number of downloads: 10

- Techfixes

I am the Pig Man
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    24th June 2018 - 08:51 PM