Creating a button that uses an input box and a msg box in MS Access VBA

Question

Hi I am really new to VBA and this forum in general, I am trying to create a button on a form, in MS Access, that when clicked will create an input box where my user can enter in a building address. After they hit ok, I want a msgbox to appear that will give them a file number.

The table I'm using has two columns, one is File_Number, which is an integer column and is set to autonumbering. My other column is my Address column which will auto populate a file number once the address is entered. `Private Sub Command39_Click()

Dim dbsFileGen As DAO.Database
Dim NewAddress As DAO.Recordset
Dim AddNew As String
Dim FileNum As DAO.Recordset
Dim FileN As Integer

Set dbsFileGen = CurrentDb
Set NewAddress = CurrentDb.OpenRecordset("dbo_File_Generator", dbOpenDynaset, dbSeeChanges)
Set FileNum = CurrentDb.OpenRecordset("dbo_File_Generator", dbOpenDynaset, dbSeeChanges)

AddNew = InputBox("Please enter the building address.")

NewAddress.AddNew
NewAddress!Address = AddNew
NewAddress.Update

End Sub

This is my code so far and when I click the button my input box comes up and the address is entered into the table just fine. However when I try to use the Move Last function to show the new file number, the msgbox keeps shows the second to last number and not the last number which would be associated with the address that was just inputed.

Hopefully I am making sense, I do apologize because I am new to VBA and new to this forum. Any help would be greatly appreciated thought.

Answer 1

Try removing the unneeded variables and check if it works:

Dim NewAddress As DAO.Recordset  
Dim AddNew As String  
     
Set NewAddress = CurrentDb.OpenRecordset("dbo_File_Generator", dbOpenDynaset, dbSeeChanges)  
     
AddNew = InputBox("Please enter the building address.")  
If AddNew = "" Then Exit Sub  
     
NewAddress.AddNew  
NewAddress!Address = AddNew  
NewAddress.Update  
     
NewAddress.MoveLast  
MsgBox NewAddress![File_Number]