Adding records from a form using VBA

[Aeonem]

Hello,
I'm currently practising the basics of database and practising making one and can't get my head around using VBA to add a record using multiple text boxes.

I want to be able to type into 5 different text boxes and then click on a button which will place the 5 values into 5 separate fields of a table.
ex. txtbox1 value goes to field1 in the table etc.

The names of the objects in the database:

Text boxes:
- txtItem
- txtSupplier
- txtStockLevel
- txtReOrderLevel


- txtBuyingPrice

Table name: tblProducts

Button name: cmdAdd

If anyone could help with the VBA code or link me to a helpful guide it would be much appreciated!

Thanks!

[amrut]

It looks like the form is unbound (does not have a record source). I think you should first learn to create and use "bound" forms. By using bound forms, you do not need any code for basic data entry/editing/deleting operations. Take a look at this tutorial (it is in Access 2007 but principle is same) http://www.techonthenet.com/access/t...7/basics02.php
One more from Microsoft Office https://support.office.com/en-ie/art...2-7e7e21e80c6b

[Aeonem]

Hey thanks for the links amrut,
I tried setting the record source before making this thread! I may have made some mistakes but when I went to add a record, it edited an existing one instead.
Will look into links though!
Thanks

[amrut]

Use the Navigation Keys at the bottom of form to move from one record to another. By default, the form always opens at the first record if there is no code forcing it to open a different record.

[ssanfu]

I'm currently practising the basics of database and practising making one and can't get my head around using VBA to add a record using multiple text boxes.

Do you have VBA code now? If so, post the code for the button.

[rpeare]

I don't use anything but unbound forms so I wrote some code to help me build data entry so that it doesn't become super tedious.

Here are the rules for using this code (attaching a basic copy of the database below)

All my table names are tblXXXXX
i.e. tblEmployee, no spaces or special characters.

When you create a form it must have the same name as the table except instead of the tbl prefix it has the prefix frm
i.e. frmEmployee

The form will have a list box on it following the same convention, this time with the prefix lst
i.e. lstEmployee

Field names on the form will correspond to those in the table WITH THE EXCEPTION OF THE PRIMARY KEY FIELD which will be named ID
The ID field's label will be named ID_Caption and the label's CAPTION will be equal to the PRIMARY KEY FIELD's name

i.e. the Primary key from table tblEmployee is Emp_ID, on the form, the FIELD will be named ID, the LABEL will be named ID_Caption, the LABEL's Caption will be Emp_ID

Each field will have a TAG value following this protocol:

DE to indicate a data entry field followed by:
N to indicate a number
T to indicate text
D to indicate a date

i.e. DED for a birth date or hire date field

each REQUIRED data element will have a tag suffix of _REQ

So for a first name (text field) that is required for a record to be saved would have a tag of

DET_REQ

Each REQUIRED field's label will be the field's name plus the suffix _Caption

i.e if the field name is Emp_LN the label's NAME will be Emp_LN_Caption

The list box will have a WHERE clause (I haven't modified the code to handle non where clauses yet), in this case I put a non null PK value which is everything in the database.

I've put in a simple table/form.

The code may be a bit beyond you at this point but by looking at the individual functions in the GLOBALFUNCTIONS module

There may be some artifacts in the GLOBALFUNCTIONS module that I didn't cut out for this example but the code for the form works as is.


Aeonem.zip

[DubCap01]

Dim Rs As Recordset
Set Rs = CurrentDb.OpenRecordset("TblWhatever", dbOpenDynaset)
Rs.AddNew
Rs![Field1] = Me.Field1
Rs![Field2] = Me.Field2
Rs.Update
Rs.Close
Set Rs = Nothing

[DubCap01]

THIS WORKS ESPECECIALLY WELL FOR UNBOUND FORMS!

With your fields in this order, and assuming each text control is named exactly as the fields in your table, place this code on the CmdAdd On Click event:

Dim Rs As Recordset
Set Rs = CurrentDb.OpenRecordset("TblProducts", dbOpenDynaset)
Rs.AddNew
Rs![txtItem] = Me.txtItem
Rs![txtSupplier] = Me.txtSupplier
Rs![txtStockLevel] = Me.txtStockLevel
Rs![txtReorderLevel] = Me.txtReorderLevel
Rs![txtBuyingPrice] = Me.txtBuyingPrice
Rs.Update
Rs.Close
Set Rs = Nothing

cheers
Pete

[Aeonem]

Hello,
I've set the record source to match with the corresponding fields, I did that before making this thread.
Is it possible to, when opening the form, that the labels have nothing in them? (No values).
As when I open the form with the labels bound, the labels will show the first record in my table. Then when trying to add a new item it just edits an the existing record that appears in the labels.

Therefore I want to be able to use any methods that will allow me to open the form with labels that have no values. Data to be entered into the labels and a button pressed that adds the record to my table.

@amrut I have the record selectors turned off to make the form look more appealing.

@ssanfu , I have no code at the moment for the button adding the records however I do have a confirmation message for the button:

Private Sub cmdAdd_Click()
response = MsgBox("Add Item?", vbYesNo)

If response = vbNo Then
MsgBox ("Macro Ending")
Exit Sub
End If
End Sub

@rpeare , I was completely lost looking through that code! Not a clue on how I could use it or translate it.

@Dubcap01 , I looked into the code and tried to get it working. However had no luck. I copied that code onto the button on click event and when trying to enter values into the labels, data could only be entered into one of them. Then when trying to press the button "cmdAdd" it just froze the form and did nothing.

[amrut]

Use this code in the form's load event or a button's click event to go to a new record. With the cursor in a textbox/combo box, PageUp and PageDown keys also take you to the previous/next records.

DoCmd.GoToRecord acDataForm,"FormName",acNewRec

Add 2 buttons to the form to navigate to previous and next records.
Replace the acNewRec with acPrevious /acNext in the above line of code and put in the Click Event of those buttons.

[DubCap01]

HI Aeonem

The code didn't work because your form could be set up incorrectly - try Amrut's suggestion, then try entering into all fields (NOT LABELS!), then try clicking the CmdAdd button

cheers
Pete

[DubCap01]

aeonem1.zip

Aeonem, I hope you can open this... one table, one form, and the code I posted works...

check it out

cheers
Pete

[ssanfu]

I'm currently practising the basics of database and practising making one and can't get my head around using VBA

Here are a couple of sites with tutorials that should help:
Access Basics By Crystal -> http://www.accessmvp.com/strive4peace/Index.htm
Rogers Access Library - Tutorials -> http://www.rogersaccesslibrary.com/forum/forum46.html

[Aeonem]

Hey,

Dubcap01 - Your example is completely what I want!

However required a lot more effort than expected. I tried to copy the code into my current practice database and it didn't work. Had some problems with not being able to type in the text boxes.
But I started from scratch to be able to upload the database onto here for help, and it ended up working on a new database. Therefore I'm just going to start from scratch with the add form working.

Thanks a lot, will be posting another thread later to try and work out how to create a search bar using VBA. I attempted it awhile back and got stuck.

Cheers

~Aeonem

[ssanfu]

I would suggest one small change to the code:
Change

Dim Rs As Recordset

to

Dim Rs As DAO.Recordset