Access VBA issues

**lgvalencia** · 03-02-2022, 03:35 PM

I have inherited an Access DB and need to modify it a little.

This is a database that can manage several programs, and each program can have multiple items associated with it. There is a letter that is created after you enter all the data. One part of the letter is a Transmittal letter number (XMTL Number) that is currently getting auto generated with VBA code.

I need to stop having it auto generate the code and allow the end user to enter what the code will be. But there is a prefix that goes before the number that is already established in the Access DB.

Click image for larger version.

Name: Main Menu.png
Views: 39
Size: 22.6 KB
ID: 47359

Here is the image of the main window. Once you select a program name then all the buttons are activated.

Click image for larger version.

Name: Program Info.png
Views: 39
Size: 48.0 KB
ID: 47360

From the Main Menu if you select the Program Update button you will get this window where the prefix number is filled in and where it will get the next number to auto generate when you go to create a letter.

Click image for larger version.

Name: Create Letter.png
Views: 39
Size: 70.7 KB
ID: 47361

On the Create letter form, is the XMTL Number field that is auto generated from the Program Update form. This field does not allow me to update or edit it.

Click image for larger version.

Name: XMTL Number Field-Got Focus Field.jpg
Views: 40
Size: 120.3 KB
ID: 47362

This is the code behind the XMTL Number Field, and I don't know what needs to be modified to keep the prefix but allow me to change the number after the prefix.

If you need more information, please ask.

Thanks for your guidance

**CJ_London** · 03-02-2022, 05:14 PM

better to post you code by copy/paste and use the code tags as previously advised.

So I can only describe where to look.

towards the bottom of the code you assign Xprefix & xchar. Think this only needs to be Xprefix

**lgvalencia** · 03-03-2022, 02:14 PM

Here is the code for the XMTL Number Got Focused field. The DB is on my work computer, so I have to capture the info and send to my home computer.

Code:

Private Sub XMTL_Number_GotFocus()
 
 
    Dim XValue As String
    Dim Xprefix As String
    Dim XChar As String
    Dim XAdmin As String
    Dim XContact As String
    Dim XPhone As String
    Dim Criteria As String
    Dim MyDB As Database
    Dim MySet As Recordset
    Dim NewNumber As Integer
 
    If IsNull(Me![Program]) Then Me![Program] = Forms![DM Main Menu]![Program]
    If IsNull(Me![XMTL Date]) Then Me![XMTL Date] = Format(Date, "DD-MMM-YYYY")
 
    If IsNull(Me![XMTL Number]) Then
        Criteria = "Program = '" & Forms![DM Main Menu]![Program] & "'"
       
        Set MyDB = CurrentDb()
        Set MySet = MyDB.OpenRecordset("Program", DB_OPEN_DYNASET)
        MySet.FindFirst Criteria              ' Find Program in table
        XValue = MySet![XMTL Number]
        XAdmin = MySet![Administrator]
        XContact = MySet![Contact]
        If Not IsNull(MySet![Contact Phone]) Then
           XPhone = MySet![Contact Phone]
        End If
 
        If Not IsNull(MySet![XMTL Prefix]) Then
            Xprefix = MySet![XMTL Prefix]
        Else
            Xprefix = ""
        End If
 
        NewNumber = (XValue + 1)
        MySet.Edit                            ' Enable editing
        MySet![XMTL Number] = NewNumber       ' Change XMTL Number
        MySet.Update                          ' Save changes
        MySet.Close                           ' Close RecordSet
   
        XChar = "" & XValue
        If (XValue > 0 And XValue < 10) Then XChar = "000" & XValue
        If (XValue > 9 And XValue < 100) Then XChar = "00" & XValue
        If (XValue > 99 And XValue < 1000) Then XChar = "0" & XValue
        [XMTL Number] = Xprefix & XChar
        [Administrator] = XAdmin        ' Store Admin Name
        [Contact] = XContact            ' Store Contact Info
        If Not IsNull(XPhone) Then
           [Contact Phone] = XPhone
        End If
    End If
 
    Forms![DM Main Menu]![XMTL Number] = [XMTL Number]
    DoCmd.GoToControl "XMTL Date"
 
End Sub

Originally Posted by Ajax

better to post you code by copy/paste and use the code tags as previously advised.

So I can only describe where to look.

towards the bottom of the code you assign Xprefix & xchar. Think this only needs to be Xprefix

**lgvalencia** · 03-08-2022, 09:03 AM

How do i change the code so that the XMTL Number field is editable? So that I can enter the suffix?

**CJ_London** · 03-08-2022, 09:22 AM

suspect nothing to do with the code - is the control enabled and unlocked?

**Welshgasman** · 03-08-2022, 11:30 AM

Posted in error

**lgvalencia** · 03-08-2022, 03:57 PM

The XMTL Number field is not locked.

I tried entering the number in the Next Transmittal Number field so it will use it for the next transmittal letter but I get an error.

Click image for larger version.

Name: Program Info.png
Views: 15
Size: 48.0 KB
ID: 47391

Click image for larger version.

Name: error.png
Views: 15
Size: 5.7 KB
ID: 47392

Click image for larger version.

Name: XMTL Number Field-Got Focus Field.jpg
Views: 15
Size: 120.3 KB
ID: 47393

I really need help to try and figure out how to enter my own suffix to the XMTL number and store that number in DB. I have been wondering if there is a size limit on the field or table or could there be a type mismatch in the code?
Thank you for your help.

**lgvalencia** · 03-08-2022, 03:59 PM

Click image for larger version.

Name: error code.png
Views: 15
Size: 65.2 KB
ID: 47394

Here is the correct pic of when I got the error code where it stopped.

**Micron** · 03-08-2022, 04:15 PM

I'm going to repeat what Ajax said -

better to post you code by copy/paste and use the code tags as previously advised.

Until I get a raise in pay here, I'm not even going to look at pictures of code.

**lgvalencia** · 03-08-2022, 04:26 PM

Code:

Private Sub XMTL_Number_GotFocus()
 
 
    Dim XValue As String
    Dim Xprefix As String
    Dim XChar As String
    Dim XAdmin As String
    Dim XContact As String
    Dim XPhone As String
    Dim Criteria As String
    Dim MyDB As Database
    Dim MySet As Recordset
    Dim NewNumber As Integer
 
    If IsNull(Me![Program]) Then Me![Program] = Forms![DM Main Menu]![Program]
    If IsNull(Me![XMTL Date]) Then Me![XMTL Date] = Format(Date, "DD-MMM-YYYY")
 
    If IsNull(Me![XMTL Number]) Then
        Criteria = "Program = '" & Forms![DM Main Menu]![Program] & "'"
       
        Set MyDB = CurrentDb()
        Set MySet = MyDB.OpenRecordset("Program", DB_OPEN_DYNASET)
        MySet.FindFirst Criteria              ' Find Program in table
        XValue = MySet![XMTL Number]
        XAdmin = MySet![Administrator]
        XContact = MySet![Contact]
        If Not IsNull(MySet![Contact Phone]) Then
           XPhone = MySet![Contact Phone]
        End If
 
        If Not IsNull(MySet![XMTL Prefix]) Then
            Xprefix = MySet![XMTL Prefix]
        Else
            Xprefix = ""
        End If
 
        NewNumber = (XValue + 1)
        MySet.Edit                            ' Enable editing
        MySet![XMTL Number] = NewNumber       ' Change XMTL Number
        MySet.Update                          ' Save changes
        MySet.Close                           ' Close RecordSet
   
        XChar = "" & XValue
        If (XValue > 0 And XValue < 10) Then XChar = "000" & XValue
        If (XValue > 9 And XValue < 100) Then XChar = "00" & XValue
        If (XValue > 99 And XValue < 1000) Then XChar = "0" & XValue
        [XMTL Number] = Xprefix & XChar
        [Administrator] = XAdmin        ' Store Admin Name
        [Contact] = XContact            ' Store Contact Info
        If Not IsNull(XPhone) Then
           [Contact Phone] = XPhone
        End If
    End If
 
    Forms![DM Main Menu]![XMTL Number] = [XMTL Number]
    DoCmd.GoToControl "XMTL Date"
 
End Sub

**lgvalencia** · 03-08-2022, 04:27 PM

I pasted the code above. The DB is on my work computer so I have to email it to myself so I can paste it here. Sorry

I appreciate you help.

**CJ_London** · 03-08-2022, 05:09 PM

step through the code and check values as you go.

It may be something to do with you appear to have a table with a field with the same name (program).

Would also help if you clarified what is what, better still use meaningful names or at least document your code to explain. is Xvalue supposed to be Next Transmittal Number?

**lgvalencia** · 03-08-2022, 06:06 PM

I inherited this code, so I don't know everything it is doing. that is why I am seeking help

**usfarang** · 03-08-2022, 06:11 PM

Simply rem out the call to the procedure if your not going to use it?
Or if your ONLY going to use part of it, rem out the other portions!
Lastly take a look at your table(s) and see what is there.
hth

**CJ_London** · 03-09-2022, 02:54 AM

I don't know everything it is doing

We can tell you what the code is doing, but without the context of the whole app, difficult to say whether any bit matters. You might be better advised to find an Access developer, provide them with the app and explain what it is supposed to do, if only from a user perspective. If you insist on doing it yourself, I've notated some of your code which may help you get started

Code:

Private Sub XMTL_Number_GotFocus()
 
 
    Dim XValue As String 'why string? apparently should be a number? relates to Next Transmittal number which now wants to be manually entered rather than calculated
    Dim Xprefix As String
    Dim XChar As String
    Dim XAdmin As String
    Dim XContact As String
    Dim XPhone As String
    Dim Criteria As String
    Dim MyDB As Database
    Dim MySet As Recordset
    Dim NewNumber As Integer
 
    If IsNull(Me![Program]) Then Me![Program] = Forms![DM Main Menu]![Program] 'where is this form? is the program value populated?
    If IsNull(Me![XMTL Date]) Then Me![XMTL Date] = Format(Date, "DD-MMM-YYYY") ' what datatype is XMTL Date? if date? why make it a string? can this be manually entered or is the control locked?
 
    If IsNull(Me![XMTL Number]) Then
        Criteria = "Program = '" & Forms![DM Main Menu]![Program] & "'"
       
        Set MyDB = CurrentDb()
        Set MySet = MyDB.OpenRecordset("Program", DB_OPEN_DYNASET) 'why not simply apply criteria to the program and not use findfirst? i.e. "SELECT * FROM Program WHERE " & Criteria
        MySet.FindFirst Criteria              ' Find Program in table 'what happens if the criteria is not met, so no record found?
        XValue = MySet![XMTL Number]
        XAdmin = MySet![Administrator]
        XContact = MySet![Contact]
        If Not IsNull(MySet![Contact Phone]) Then
           XPhone = MySet![Contact Phone]
        End If
 
        If Not IsNull(MySet![XMTL Prefix]) Then
            Xprefix = MySet![XMTL Prefix]
        Else
            Xprefix = ""
        End If
 
        NewNumber = (XValue + 1) 'Xvalue is a string
        MySet.Edit                            ' Enable editing
        MySet![XMTL Number] = NewNumber       ' Change XMTL Number
        MySet.Update                          ' Save changes
        MySet.Close                           ' Close RecordSet
   
        XChar = "" & XValue
        If (XValue > 0 And XValue < 10) Then XChar = "000" & XValue 'xvalue is a string, use format(XValue,"0000") then other lines not required
        If (XValue > 9 And XValue < 100) Then XChar = "00" & XValue 'xvalue is a string
        If (XValue > 99 And XValue < 1000) Then XChar = "0" & XValue 'xvalue is a string
        [XMTL Number] = Xprefix & XChar
        [Administrator] = XAdmin        ' Store Admin Name
        [Contact] = XContact            ' Store Contact Info
        If Not IsNull(XPhone) Then
           [Contact Phone] = XPhone
        End If
    End If
 
    Forms![DM Main Menu]![XMTL Number] = [XMTL Number]
    DoCmd.GoToControl "XMTL Date"
 
End Sub

Access VBA issues

Thread Tools

Access VBA issues

Access Database XMTL Number

Similar Threads

From access 2003 to access 2007 issues with a module

Access Database Issues

Access compatibility issues?

Basic access issues

Access security issues

Posting Permissions