INSERT INTO table in access from inputbox

Question

I'm stumped - and also very new to VBA in access but here is my intent:

I have want the member to select a "Begin" button in access, from there, multiple inputboxes will pop up allowing the user to enter data. I want the data entered (name, date, etc.) to be stored into a label on the form, and at the end the next person who opens the "begin" button has a fresh bunch of inputboxes pop-up and their data is then stored in each label of form. End game, a pretty form is emailed to me.

I've mastered the inputboxes and msgboxes - they pop up and run all the way through. The problem I'm at is when the user enters their information I need it to go onto the form into the appropriate fields so in the end the form can be "printed" as a .pdf and sent to me. Any help would be greatly appreciated!

Sub Begin()

Dim AddName As String
Dim AddAddress As String
Dim AddSupv As String
Dim AddPhone As String

AddName = InputBox("What is your rank and full name?", "Enter Rank and Name")
AddAddress = InputBox("What is your home address? Please include street, city, state and zip.", "Enter Address")

Where is any, would I add a function to insert the data after each inputbox into a field on my form. Form Name is Form1 and I also have a table labeled 'AUS Table' that have fields called 'Name_and_Rank' and 'Address' if the inputbox data must go to a table first.

If you have a form then why not just let users enter data into textboxes instead of annoying popups that have no data validation? — June7
That is going to be one of my options, however, the form required is wordy and confuses most members. Not to mention, members usually either initial spots they shouldn't, or skip over sections they should out of confusion. With the pop-ups, it forces them one direction or another and will create less user error. — Heather Dragon
Less user error? I rather doubt it. Validating popup prompt input is tricky - can be done but the VBA is onerous. Users will enter bad values and get frustrated when they don't understand why their inputs are rejected and then can even result in run-time errors if you don't do validation. You are new to VBA so until you get better at programming use Access as it was designed to be used - enter data into form controls. Improve the form, use Tab control to better organize, use ValidationRule property and some VBA to manage required inputs and/or make controls available/not available. — June7
Also, explore Conditional Formatting for textboxes and comboboxes. — June7
If I explore that route of a form, is there a way to use a button per page where the user can input one bit of information (name), and then select "next" using a button on that page? Or a "yes/no" button on a page that would direct them to the next section they need to be on? my end goal is still that all the data can be submitted onto one form in the respective areas, and printed out/emailed to me. — Heather Dragon

June7 June7 · Accepted Answer · 2020-04-27T16:50:32

Variables are not needed, just reference controls.

For label controls:
Me.lblName.Caption = InputBox("What is your rank and full name?", "Enter Rank and Name")
Me.lblAddress.Caption = InputBox("What is your home address? Please include street, city, state and zip.", "Enter Address")

For textbox controls:
Me.tbxName = InputBox("What is your rank and full name?", "Enter Rank and Name")
Me.tbxAddress = InputBox("What is your home address? Please include street, city, state and zip.", "Enter Address")

But if there are textboxes on form, users should just type directly into textboxes.

INSERT INTO table in access from inputbox

1 Answers