This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(3.2, 92%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECN%3C/text%3E%3C/svg%3E)
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.
I suggest you delete the dotnet-visual-basic tag and replace it with office-access-dev. The dotnet-visual-basic tag is intended for questions about writing managed code using VB.Net, not VBA.
Which code are you using to display the number, and where did you put it?
Whoops, got it, thank you for that I appreciate it.
I didn't realize that the last little bit got left off, this is the full code that I am using
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 String
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
FileNum.MoveLast
MsgBox (FileNum![File_Number])
End Sub
The input box will come up just fine and I can enter a value into the address column just fine as you can see in the next screenshot.
An when I click ok the MsgBox appears with a number which is what I wanted
However it is not the last record in the table, which is the number I want to show in the box, shown in this last screenshot:
Any suggestions are greatly appreciated, I am very much a novice at this. Thank you so much for the help.
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]
That worked perfectly!!! Thank you so much for the help!!!