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

Cole Nelson 21 Reputation points
2022-08-26T22:25:13.337+00:00

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.

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
832 questions
{count} votes

Accepted answer
  1. Viorel 112.9K Reputation points
    2022-08-27T05:26:30.217+00:00

    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]  
    

0 additional answers

Sort by: Most helpful