MS ACCESS get SQL statement to run and return value to be used elsewhere

Question

HELP ! :)

I have some VBA code in my Access DB, that I can't seem to get working. I want a button to start a SQL SELECT statement, and then return the value it gets.

I need this to allow users to enter a letter in the input box, but the value I need it to be is a number. Therefore I tried using this SQL statement to return the number from the table Eddition_Aide which has all the editions (a, b, c, d, etc) and their corresponding id number.

But I can't seem to get it working.

This is the code:

Dim NewEd As String
    
Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec

NewEd = InputBox("Enter new edition")
TempVars("xNewEd") = NewEd

Dim SQLdata As String
SQLdata = "SELECT TB_Eddition_Aide.ID_Key " & _
          "FROM TB_Eddition_Aide " & _
          "WHERE TB_Eddition_Aide.Edditions_Txt=[Tempvars]![xNewEd];"

Me.ED_Start_Date = InputBox("Enter first Start Date", , Date)
Me.AllowAdditions = False
DoCmd.Save

Answer 1

Try something like this:

Dim NewEd   As Long
Dim NewKey  As Long
Dim NewDate As Variant
    
Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec

NewEd = Val(InputBox("Enter new edition"))
If NewEd > 0 Then
    NewKey = Nz(DLookup("ID_Key", "TB_Eddition_Aide", "Edditions_Txt = " & NewEd & ""))
    If NewKey > 0 Then
        NewDate = InputBox("Enter first Start Date", , Date)
        If IsDate(NewDate) Then
            Me!ED_Start_Date.Value = CDate(NewDate)
            Me.Dirty = False
        End If
    End If
End If
Me.AllowAdditions = False