Save content textbox to record in table

[Jerome]

Hello,

I have a few text boxes filled with data from my data table. When I modify some of the data in the textbox I would like to store it into my data table (I know the record number of the record)

What is the best VBA way to do this?

I was hoping for something like this:

TblData.record(x).column(0) = forms("Test").textbox1.text (But this is the wrong syntax I think)

Thanks in advance.

 

[DCrake]

You need it to be a bound form with textboxes bound to specific fields in your table.

David

 

[Jerome]

Ok thanks,

Now I just want to know:

How do I create a bound form and how do I create text boxes that are specifically bound to certain fields in the table?

Thanks for you help so far. I am getting familiar wit Access pretty fast due to the good forum support.

Thanks in advance

 

[Jerome]

Sorry for the double post

 

[DCrake]

Have you tried creating a form using the forms wizard?

 

[Jerome]

No, the form is created using the create --> form method.

 

[branston]

Is the table finding information already stored in the table, or is it just going to store something? Can you explain a bit more?

 

[Jerome]

Ok some more explenation:

I have a table with 8 columns and 500 rows (for now).
I use a searchform to search the datatable, the data that meets the search criteria are shown in a listbox in the search form.
When a row in the listbox is doubleclicked, a new form is opened. In this form a few textboxes are placed that show some of the data from the previous selected row (record). This part already works
When the user edits one of the textboxes I would like to store the data in the table, that is what I would like to accoplisch now.

I've made a big part of this program using VBA, this because I alredy have some VBA programming skills (Excel).

With best regards.

 

[Jerome]

Can anyone please give some advice? I have searched the forum and internet but without success.

Thanks in advance.

 

[Kryst51]

When the user edits one of the textboxes I would like to store the data in the table, that is what I would like to accoplisch now.

This form that opens, is it bound to a table? Can you make it like any other form, and then set it to pull up the record that was double clicked in your previous forms list box? I am sure there is plenty of threads available to show you how to code that form to do that. My two cents, literally...

 

[Scooterbug]

How is the data getting to the form that pops up? Are you manually populating the form?

The best thing to do is to base the form off a query that has the data you wish to display. You can then apply a filter based off the Primary key from the row of data in the list box.

 

[Jerome]

In the Pop up form I have the following code:

Private Sub Form_Activate()
Me.TextRecordNr = Forms("frmsearch").List.Column(0)
Me.TextName = Forms("frmsearch").List.Column(1)
Me.TextIndexNr = Forms("frmsearch").List.Column(2)
Me.TextLocation = Forms("frmsearch").List.Column(3)
Me.TextStock = Forms("frmsearch").List.Column(4)
Me.TextStockLocation = Forms("frmsearch").List.Column(5)
Me.TextDateIn = Forms("frmsearch").List.Column(6)
Me.TextCode = Forms("frmsearch").List.Column(7)
End Sub

When I double click a row in the listbox the data is pulled out of the listbox it in that way.

I don't know if this is the best way to do this?

 

[Scooterbug]

If you want to be able to edit the data, the best way is bind the form to the table. What you are doing is just pulling the data from the list box and populating the form. You can still use this, but you will have to run an update query. This will still require you to be able to identify the record that has the data in it.

I am assuming that all the data you have in the list box is based on a single table. If so, all you need to do is include the Primary Key for the record in the list box. You dont have to display the actual number. By making the Primary Key the bound field from the list box, you can set criteria for the query for the pop up form.

If you would like, post your database and I can show you what I mean if it sounds confusing (I do better by showing that by typing )

 

[Jerome]

Well it does not sound confusing to me, but I can really use some help

All the data that I use is coming from one table.
The primary key is shown in the listbox.

Here's the database:

Open the "FrmSearchChemicals" To start the database. Then type something in the enter search string textbox for example (li), the listbox should be filled.
Now double click a row. "FrmEditRecord"is opened and the data is shown.

Another question: When the "new Record" button is clicked in the "FrmSearchChemicals" form , I would like to create a new record so data can be entered in the "FrmNewRecord". Is this also possible?

Thanks in advance

 

[Scooterbug]

Here ya go. Made one change to your chemical table. I made the record number the primary key. Always good to have a primary key, as that is what makes a record unique.

I set the source of your Edit form and New Record form to that of the chemical table. With the edit, I stipulated that the only record that should show is where the Record number in the edit form is equal to the record number of the list box. Now, if you edit the data on the Edit form, it will update it in the chemical table as well.

As for the new record form, It is bound to the chemical table as well. When it opens, it will automatically go to a new record.

 

[Jerome]

Wow, Thats fast Thank you very much!!, this is exactly what I need.

How did you set this primary key?

 

[Scooterbug]

to set the primary key, go into the table design. Click on the row that you want to be the primary key...then in the tool bar, you will see a button that looks like a key. click on that and the grey box on the left of the row will display a key. That will indicate that it's the primary key.

 

[Jerome]

Great and thanks for the help.

 

[sunkerala]

Here ya go. Made one change to your chemical table. I made the record number the primary key. Always good to have a primary key, as that is what makes a record unique.

I set the source of your Edit form and New Record form to that of the chemical table. With the edit, I stipulated that the only record that should show is where the Record number in the edit form is equal to the record number of the list box. Now, if you edit the data on the Edit form, it will update it in the chemical table as well.

As for the new record form, It is bound to the chemical table as well. When it opens, it will automatically go to a new record.

Hi Scooterbug
in the above file u have uploaded, in the edit form as well as new form the data is saved only on the next record button clicked(right arrow key at the bottom of the form), how to enable the save button or update button to save the data to table

 

[Gasman]

@sunkerala
You might want to start your own thread?
You have posted in an almost 11 year old thread.?