Question Need VB coding for access data base to save the value in text box using save button (1 Viewer)

JAWAJANA

New member
Local time
Tomorrow, 01:15
Joined
Dec 19, 2017
Messages
3
Hi

I have a table with the heading and the text box name as mention below

I have a save button in the Form

I need VB coding to save the date which is update in the text box to the Table and once update need to get a message box saying it is saved and all the value in the text box should be empty to update new data

Please help

Table Name is (SALESTABLE)

Contract Date (Table heading) - (T1 Test box Name)
First shipment(Table heading) - (T2 Test box Name)
Last Shipment(Table heading) - (T3 Test box Name)
Customer(Table heading) - (T4 Test box Name)
Country(Table heading) - (T5 Test box Name)
Market(Table heading) - (T6 Test box Name)
BRM Month(Table heading) - (T7 Test box Name)
Contract Number(Table heading) - (T8 Test box Name)
Code(Table heading) - (T9 Test box Name)
BRM Category(Table heading) - (T10 List box Name)
Micro(Table heading) - (T11 Test box Name)
Original Contract Price(Table heading) - (T12 Test box Name)
INCO TERMS(Table heading) - (T13 Test box Name)
Contract Price FOB Basis(Table heading) - (T14 Test box Name)
Entire Contract Qty(lbs)(Table heading) - (T15 Test box Name)
Contract This Year(Table heading) - (T16 Test box Name)
Contract Next Year(Table heading) - (T17 Test box Name)
ITEM#(Table heading) - (T18 Test box Name)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:45
Joined
May 7, 2009
Messages
19,169
you should make your Form Bound
and its Record Source set to
Table Heading. therefore no need
to have a Save button. the changes
you make to the fields are automatically
saved to the table.

otherwise, if it is your choice to have
the form unbound, you use
Update Query and call it in the
Click Event of your button.
But first you add an Autonumber field
ont Table Heading, (eg. ID).
this way we can uniquely identify
which record to update:

Private Sub button_Click()
Dim strSQL As String
strSQL = strSQL & "Update [Table Heading] Set "
strSQL = strSQL & "[Contact Date]=@1,"
strSQL = strSQL & "[First Shipment]=@2,"
strSQL = strSQL & "[Last Shipment]=@3,"
strSQL = strSQL & "[Customer]=@4,"
strSQL = strSQL & "[Country]=@5,"
strSQL = strSQL & "[Market]=@6,"
strSQL = strSQL & "[BRM Month]=@7,"
strSQL = strSQL & "[Contract Number]=@8,"
strSQL = strSQL & "
Code:
=@9,"
strSQL = strSQL & "[BRM Category]=@10,"
strSQL = strSQL & "[Micro]=@11,"
strSQL = strSQL & "[Original Contract Price]=@12,"
strSQL = strSQL & "[INCO TERMS]=@13,"
strSQL = strSQL & "[Contract Price FOB Basis]=@14,"
strSQL = strSQL & "[Entire Contract Qty]=@15,"
strSQL = strSQL & "[Contract This Year]=@16,"
strSQL = strSQL & "[Contract Next Year]=@17,"
strSQL = strSQL & "[ITEM#]=@18;"

Call UpdateTable(strSQL, [T1],[T2],[T3],[T4],[T5],[T6],[T7],[T8],[T9],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18])
End Sub

Private Sub UpdateTable(ByVal strSQL As String, ParamArray p() As Variant)
Dim i As Integer
Dim qd As DAO.QueryDef
With Currentdb.CreateQuerydef("", strSQL)
	For i = 0 To Ubound(p)
		.Parameters(i)=p(i)
	Next
	.Execute
End With
End Sub
 

Ranman256

Well-known member
Local time
Today, 15:45
Joined
Apr 9, 2015
Messages
4,339
you dont need any vb code.
bind the form to a table and use queries to add/change data.

The beauty of Access is you rarely need vb code.
 

JAWAJANA

New member
Local time
Tomorrow, 01:15
Joined
Dec 19, 2017
Messages
3
I am Getting Run time error 3061

Too Few Parameter. Expected 20.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:45
Joined
May 7, 2009
Messages
19,169
are you Updating existing record or
Adding new one.
this is the code for Adding.

Private Sub button_Click()
Dim strSQL As String
strSQL = strSQL & "Insert Into [Table Heading] ("
strSQL = strSQL & "[Contact Date],"
strSQL = strSQL & "[First Shipment],"
strSQL = strSQL & "[Last Shipment],"
strSQL = strSQL & "[Customer],"
strSQL = strSQL & "[Country],"
strSQL = strSQL & "[Market],"
strSQL = strSQL & "[BRM Month],"
strSQL = strSQL & "[Contract Number],"
strSQL = strSQL & "
Code:
,"
strSQL = strSQL & "[BRM Category],"
strSQL = strSQL & "[Micro],"
strSQL = strSQL & "[Original Contract Price],"
strSQL = strSQL & "[INCO TERMS],"
strSQL = strSQL & "[Contract Price FOB Basis],"
strSQL = strSQL & "[Entire Contract Qty],"
strSQL = strSQL & "[Contract This Year],"
strSQL = strSQL & "[Contract Next Year],"
strSQL = strSQL & "[ITEM#]) "
strSQL = strSQL & "SELECT " & _
strSQL = strSQL & "@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18;"

Call UpdateTable(strSQL, [T1],[T2],[T3],[T4],[T5],[T6],[T7],[T8],[T9],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18])
Msgbox "Record saved to table."
' empty textboxes
Dim i As Integer
For i = 1 TO 18
	Me.Controls("T" & i)=Null
Next
Me.T1.SetFocus
End Sub

Private Sub UpdateTable(ByVal strSQL As String, ParamArray p() As Variant)
Dim i As Integer
Dim qd As DAO.QueryDef
With Currentdb.CreateQuerydef("", strSQL)
	For i = 0 To Ubound(p)
		.Parameters(i)=p(i)
	Next
	.Execute
End With
End Sub
 

JAWAJANA

New member
Local time
Tomorrow, 01:15
Joined
Dec 19, 2017
Messages
3
NOW i am getting Run time error 3129

Invalid SQL Statement; expected 'DELETE','INSERT','PROCEDURE','SELECT' OR 'UPDATE'

AFTER UPDATING BELOW CODE

Private Sub Save_Click()
Dim strSQL As String
strSQL = strSQL & "Insert Into [SalesTable] ("
strSQL = strSQL & "[Contact Date],"
strSQL = strSQL & "[First Shipment],"
strSQL = strSQL & "[Last Shipment],"
strSQL = strSQL & "[Customer],"
strSQL = strSQL & "[Country],"
strSQL = strSQL & "[Market],"
strSQL = strSQL & "[BRM Month],"
strSQL = strSQL & "[Contract Number],"
strSQL = strSQL & "
Code:
,"
strSQL = strSQL & "[BRM Category],"
strSQL = strSQL & "[Micro],"
strSQL = strSQL & "[Original Contract Price],"
strSQL = strSQL & "[INCO TERMS],"
strSQL = strSQL & "[Contract Price FOB Basis],"
strSQL = strSQL & "[Entire Contract Qty],"
strSQL = strSQL & "[Contract This Year],"
strSQL = strSQL & "[Contract Next Year],"
strSQL = strSQL & "[Item Number]) "
strSQL = strSQL & "SELECT " & _
strSQL = strSQL & "@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18;"

Call UpdateTable(strSQL, [T1], [T2], [T3], [T4], [T5], [T6], [T7], [T8], [T9], [T10], [T11], [T12], [T13], [T14], [T15], [T16], [T17], [T18])
MsgBox "Record saved to table."
'empty textboxes
Dim i As Integer
For i = 1 To 18
Me.Controls("T" & i) = Null
Next
Me.T1.SetFocus
End Sub

Private Sub UpdateTable(ByVal strSQL As String, ParamArray p() As Variant)
Dim i As Integer
Dim qd As DAO.QueryDef
With CurrentDb.CreateQueryDef("", strSQL)
For i = 0 To UBound(p)
.Parameters(i) = p(i)
Next
.Execute
End With
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:45
Joined
May 7, 2009
Messages
19,169
Ooops, sorry about that.
can you spot on the last
part of:

strSQL = strSQL & "SELECT " & _

remove the ampersand and the
underscore ( & _ ).
 

Users who are viewing this thread

Top Bottom