Avatar of shieldsco
shieldsco
Flag for United States of America asked on

Access alphanumeric value

The following code works when the user enters an alpha value followed by a numeric value (LL-15-KH) however when the user enters a numeric value first followed by an alpha value (15-HH-YY-2010) the code does not work. I get a type mismatch. I'm think the problems lies in the following statement; tdf.Fields(strFieldName).DefaultValue = " " & Text & " "
Any thoughts

 
Public Sub ChangeTableFieldDefaultValueText(strTableName As String, strFieldName As String, Text As String, strFormName As String)
On Error GoTo Err_ChangeTableFieldDefaultValue

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
   
   
    Set db = CurrentDb()
    Set tdf = db.TableDefs(strTableName)
   
    tdf.Fields(strFieldName).DefaultValue = " " & Text & " "
   
        're-open calling form if name is passed
    If strFormName <> "" Then
        DoCmd.OpenForm strFormName, acNormal
    End If

Exit_ChangeTableFieldDefaultValue:
    Set tdf = Nothing
    Set db = Nothing
    Exit Sub

Err_ChangeTableFieldDefaultValue:
    MsgBox Err.Number & " (" & Err.Description & ") in procedure ChangeTableFieldDefaultValue of Module DefaultsText"
    Resume Exit_ChangeTableFieldDefaultValue

End Sub
Microsoft Access

Avatar of undefined
Last Comment
shieldsco

8/22/2022 - Mon
mbizup

Your code seems to work for me regardless of whether a alpha string or a number starts the value of text.

How are you actually calling this procedure? This seems to work for me:

call ChangeTableFieldDefaultValueText("tblPeople","Country","14-12-123","")

I think the problem may be in how the procedure is called, or in the datatype of the underlying field (is it text, numeric, date...?)
Jeffrey Coachman

...Why are the space bar characters needed?
" "
a Spacebar charcter (" " ) is Text, so the number may give this problems...

What is : "Text"
?
A variable?
...a string?



So first try this and see if it works:
tdf.Fields(strFieldName).DefaultValue = Text

Or try this to convert the number to a string:
tdf.Fields(strFieldName).DefaultValue = " " & cstr(Text) & " "


JeffCoachman
ASKER CERTIFIED SOLUTION
mbizup

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mbizup

An uglier alternative, lots of double quotes.  To enclose double quotes in a string, you need to double up on them:

tdf.Fields(strFieldName).DefaultValue = """" & Text & """"

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
shieldsco

ASKER
The undelying field is text
Text is a string
Jeff - the resuslts are the same after using your code.

Here is the complete code:
Module:
Public Sub ChangeTableFieldDefaultValueText(strTableName As String, strFieldName As String, Text As String, strFormName As String)
On Error GoTo Err_ChangeTableFieldDefaultValue

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
   
   
    Set db = CurrentDb()
    Set tdf = db.TableDefs(strTableName)
   
    tdf.Fields(strFieldName).DefaultValue = " " & Text & " "
   
        're-open calling form if name is passed
    If strFormName <> "" Then
        DoCmd.OpenForm strFormName, acNormal
    End If

Exit_ChangeTableFieldDefaultValue:
    Set tdf = Nothing
    Set db = Nothing
    Exit Sub

Err_ChangeTableFieldDefaultValue:
    MsgBox Err.Number & " (" & Err.Description & ") in procedure ChangeTableFieldDefaultValue of Module DefaultsText"
    Resume Exit_ChangeTableFieldDefaultValue

End Sub

Input Form:
Private Sub Text1_AfterUpdate()
On Error GoTo Err_Text1_AfterUpdate

    Dim TextInput As String
    Dim strTableName As String, strFieldName As String, strFormName As String
    Dim varInput As Variant
    Dim arrArgs() As String
   
    varInput = Me.Text1.Value
    TextInput = (varInput)
   
        'split OpenArgs string on comma delimiter
    arrArgs = Split(Me.OpenArgs, ",")
   
        'get values from array
    strTableName = arrArgs(0)
    strFieldName = arrArgs(1)
    strFormName = arrArgs(2)
   
        'call procedure to change underlying table field default value
    Call ChangeTableFieldDefaultValueText(strTableName, strFieldName, TextInput, strFormName)
   
        'close this form
    DoCmd.Close acForm, Me.Name
   

Exit_Text1_AfterUpdate:
    Exit Sub

Err_Text1_AfterUpdate:
     MsgBox "Default Value Updated.......", vbExclamation
    Resume Exit_Text1_AfterUpdate

End Sub

Main Form:
Private Sub DMAgreementNo_DblClick(Cancel As Integer)
On Error GoTo Err_Command4_Click

    Dim strFormName As String, strTableName As String, strFieldName As String

    strFormName = Me.Name
    strTableName = Me.RecordSource
    strFieldName = Me.DMAgreementNo.Name

   
   
   
        'open input form passing source table name, table column name and current form name
        'as comma delimited string the OpenArgs argument
    DoCmd.OpenForm "frmInputText", acNormal, , , , , strTableName & "," & strFieldName & "," & strFormName
   
        'close this form
    DoCmd.Close acForm, strFormName

Exit_Command4_Click:
    Exit Sub

Err_Command4_Click:
    MsgBox Err.Number & " (" & Err.Description & ") in procedure Command4_Click of VBA Document Form_Project"
    Resume Exit_Command4_Click
End Sub
mbizup

I think we cross-posted.

Try either of these methods.  They are equivalent to each other, and are basically a programatic way of putting quotes around your default value in the Field Properties in the table's design.

tdf.Fields(strFieldName).DefaultValue = Chr(34) & Text & Chr(34)

Open in new window


tdf.Fields(strFieldName).DefaultValue = """" & Text & """"

Open in new window

shieldsco

ASKER
Thanks a lot
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.