Use a Form to Add Data to Table

[Djblois]

I created a form that I want to use to add to a table. The table only has one column. I would like it so that when I open the form it has the blank textbox that I already added and then the user has to type in the item to add and the click the button "Add New Carrier" to add it to the table. Sorry if this is easy but I am new to Access and I have been looking for an answer.

 

[pbaldy]

If you're saying you want the form open to a new record, you can set its Data Entry property to Yes.

 

[Djblois]

Yes, but I also do not want it to save the new entry until the user clicks a button that I create.

 

[John Big Booty]

What Paul has suggested will pretty much do that, all you will need to do is prevent the user from tabbing to a new record or using one of the other control on the form to go to a new record without clicking your button.

To overcome problem one put a piece of code in the "on lost focus" event of the button to set the focus back to the previous control in the tab control list.

To over come the second problem, set the forms "control box" to No.

 

[CEH]

I would first wonder why you have a table with one "column". Do you mean field? If so, why only one field?

 

[Djblois]

The reason it has only one field is because I just need it to keep track of the names of trucking companies in order for it to be able to be used to input them in another table from a drop down. I wanted to use the table because then I can set it up so that the users can add another trucking company to it later - they won't need me to add it.

 

[Djblois]

I want to have two separate buttons on the form. One for Add Carrier (which should add the new carrier to the table and then clear the textbox and keep the form open) and then a separte button to close it. How do I accomplish the first button.

 

[DevastatioN]

What I do for my users is make them a new form that lists the dropdown list in datasheet view, so they can add a new company or whatever they need to the bottom of the list.

If you really want it to only add after the button is clicked then you can use...

Dim dbCurrent As DAO.Database
Dim rstData As DAO.Recordset
Dim data as string

data = me.txtEnter

Set dbCurrent = CurrentDb
Set rstData = dbCurrent.OpenRecordset("Table1")

rstData.AddNew
rstData("Field").Value = data
rstData.Update

set rstData = nothing

This code should update the table correctly. That way you can have your cancel button exit the form without updating anything. There is a simpler way most likely, which is cancelling the record entry upon hitting the cancel button.

 

[Djblois]

That works except I had to remove one line:

rstData.Update

However, How well would this work for a table with multiple fields. I understand that I will have to add multiple string variable and do a seperate line for each field but would it add only one entry if done this way or would it add an entry with the first field for one entry and then add one entry for the second field and so on?

 

[DevastatioN]

I'm not sure why rstData.update had to be removed, but glad it works.

How it works is, the recordset stays on the exact same row unless you tell it otherwise. The .addnew means it creates a new entry, and that line is the current line.

Meaning you can enter multiple different fields, that all relate to the same record.

Just do:

rstData.AddNew
rstData("Field").Value = data
rstData("Field2").Value = data2
rstData("Field3").Value = data3

and so on.

If you wanted to add multiple records, then you have to put another .addnew in the code. For example:

rstData.AddNew
rstData("Field").Value = data
rstData("Field2").Value = data2
rstData("Field3").Value = data3
rstData.AddNew
rstData("Field").Value = data4
rstData("Field2").Value = data5
rstData("Field3").Value = data6

Would add two new records to your table, the first three entries are for a single record, and the last three entries aer for the second single record.

Hope this answers your question.

 

[John Big Booty]

The way this situation is handled in the Access templates is to have a pieces of code in the "Not in list" event, of the combo that instructs the user to double click (on the combo) to add to the list. Then in the "On Double click" event the code opens up a form, linked to the table underlying the combo, at a new record the user can then add whatever values are required to the table. When that new form is closed the combo is re-queried. Check out the contact management template in your Access templates, for an example.

In a fully normalised DB this table would have at least two filed (columns) one an auto number Primary Key (PK), the other the information (in your case the trucking co name). The PK is then stored in the table feeding your main form as a foreign key.