insert the value from a txtbox into a table

[spnz]

Hi there

Can someone give me a basic example.

I have a form that contains a textbox and a listbox. The listboxs rowsource is tblSPNZ.
The table has 2 fields 1 is and autonumber and the other is where I want the results to go.

How do I make a command button populate the table? Avoiding putting any results into the autonumber field?


Thank you for your help.

 

[ScottGem]

I'm not sure I understand the problem. If you are storing the selection in the listbox, just set the Controlsource of the listbox to the field in your table.

 

[spnz]

Hi Scott.
The txtbox isn't bound to anything.
What I am trying to achieve is by pressing a command button the value in the textbox been updated to the table.
The listbox is used to display the values in tblSPNZ.

Does that make sense?

 

[ScottGem]

Not really. One, its not clear what's in the text box. Two, it would be simpler to just use a bound form and bound controls. Therefore no need for a command button to add anything to the table.

 

[spnz]

Ok...

The textbox contains no information. I want to use that as an input box so whatever value I put in the textbox and by pressing the command button it is transfered to the table. The listbox is used so the person entering the information can see what is already in the table.

I am making a form that has a few option buttons on it. A listbox & a textbox
With the press of the different optbuttons the rowsource of the listbox is changed to the selected table via the optionbutton. The listbox then displays the records from the choosen table.

I have a few tables that contain things like Managers name, consultants names. All tables that are not changed very often but I would rather that a form is used to update them instead of someone going into the tables to update.

 

[ScottGem]

Let me see if I understand this. You have a list box and you have option buttons that change the Rowsource of that list box based on the selection. You then input something into a text box that is supposed to edit something in one of the records shown in the list box.

Is that it? If so, you need to define a) what field you are editing and b) what record you are editing. How are you doing that?

 

[spnz]

Yes you got it.

The listboxs contain 2 columns . Column 1 is to show the primary key from the table (autonumber) and the 2nd column will display the information that has come from the textbox.
The form contains a group of options. Using the after update function the listbox is populated with whatever choice the users make.
e.g

Case 1
Me.lstTableItems.RowSource = "tblConsultant"

Case 2
Me.lstTableItems.RowSource = "tblWhatever"

My problem is I do not know how to get the information from the textbox into the desired table.

Thanks Scott for your help

 

[ScottGem]

We are close, but I still don't understand something. You say "2nd column will display the information that has come from the textbox.". Do you mean that the data entered in the textbox will be used to update the second column of the of the textbox?

If so, what you can do is something like this:

Dim strSQL As String

SELECT Case Me!optGroup
Case 1
strSQL = "UPDATE tblConsultant SET fieldname = '" 

Case 2
strSQL = "UPDATE tblWhatever SET fieldname = '" 

etc.

End Select

strSQL = strSQL & Me!txtcontrol & "':;"

Currentdb.Execute strSQL

Me!listbox.Requery

 

[spnz]

Hi Scott

Thanks for your help!!
I have used your code you gave me.......When I excute the code.The code changes all the current values in the field are changed to the value from the textbox.
How do I make it not change all the records within that field but add the new record into that field?

This is the code I have used.

Dim strSQL As String
    
Select Case Me!grpEdit
Case 1
strSQL = "UPDATE tblVisaType SET VisaType = '"

End Select

strSQL = strSQL & Me!txtTransferBox & ":';"

CurrentDb.Execute strSQL

Me!lstTableItems.Requery
End Sub

Thanks again!!

 

[ScottGem]

Sorry my fault. I left out a key piece, a Where clause. Here's revised code:

Dim strSQL As String
Dim strSQLW As String
    
Select Case Me!grpEdit
Case 1
strSQL = "UPDATE tblVisaType SET VisaType = '"
strSQLW = " WHERE keyfield = " & Me!lstTableItems & ";"

End Select

strSQL = strSQL & Me!txtTransferBox & strSQLW

CurrentDb.Execute strSQL

Me!lstTableItems.Requery
End Sub

 

[spnz]

Hi Scott
Sorry to get hassling you with this but I am having a mare during to get it to work.

I have tried changing yuor code but I get a runtime error '3075'
Syntax error in string in query expression "blabla WHERE VisaType = ;.

This is the changed code

Private Sub cmdDoit_Click()
Dim strSQL As String
Dim strSQLW As String
    
Select Case Me!grpEdit
Case 1
strSQL = "UPDATE tblVisaType SET VisaType = '"
strSQLW = " WHERE VisaType = " & Me!lstTableItems & ";"

End Select

strSQL = strSQL & Me!txtTransferBox & strSQLW

CurrentDb.Execute strSQL

Me!lstTableItems.Requery
End Sub

Thanks for all your help it is appreciated.

 

[ScottGem]

You need to stop and look at what you are doing here. By understanding the process (which is what my previous questions led to), you can figure out what is wrong.

The process as I understand it, is you want to select a record from a list box and update some field in that record with a value entered into a text box. Because the listbox can querying different tables, you have to do this in code rather then using bound controls.

So, your code has to specify a table, a field, the new value and identify the specific record.

In my example you assign strSQL based on the table and field name:
UPDATE table SET fieldname =
table and field name will vary depending on the selection in the option group.

Next you concatenate in the value from the textbox

Finally, you set your WHERE clause using the bound value of the listbox. I don't see where using VisaType if valid for the WHERE clause since that is what you are updating.

Frankly, your initial posts indicated updating some name, VisaType doesn't really make sense in your scenario.

I really suggest that you review exactly what you are trying to do here. Its looking to me like you are hung on on a process that doesn't fit your needs.