Please help in VBA

[Jazz]

Hi Folks,

I am working on a database tool for quality team.

I have around 30 text box on a form and trying to insert the value of those 30 text boxes in a table from vba.

here is my code

Private Sub Submit_Click()
Dim a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, aa, bb, cc, dd As String
Dim SQL As String


a = Month
b = Week
c = Type_of_Contact
d = Country
f = Department
g = Associate_name
h = Measurement_Date
i = Query_type
j = Reviewer_Name
k = Witness_Id
l = Barcode_Voucher_No
m = Case_ID
n = Route_cause
o = Error_Status
p = Catagory1
q = Error_type1
r = Impact1
s = Catagory2
t = Error_type_2
u = Impact2
v = Catagory3
w = Error_type_3
x = Impact3
y = Catagory4
z = Error_type_4
aa = Impact4
bb = Catagory5
cc = Error_type_5
dd = Impact5


SQL = "Insert into [Final Data](a,b,c,d,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,aa,bb,cc,dd) values ( '" & Me.Month & "','" & Me.Week & "','" & Me.Type_of_Contact & "','" & Me.Country & "','" & Me.Department & "','" & Me.Associate_name & "','" & Me.Measurement_Date & "','" & Me.Query_type & "','" & Me.Reviewer_Name & "','" & Me.Witness_Id & "','" & Me.Barcode_Voucher_No & "','" & Me.Case_ID & "','" & Me.Route_cause & "','" & Me.Error_Status & "','" & Me.Catagory1 & "','" & Me.Error_type1 & "','" & Me.Impact1 & "','" & Me.Catagory2 & "','" & Me.Error_type_2 & "','" & Me.Impact2 & "','" & Me.Catagory3 & "','" & Me.Error_type_3 & "','" & Me.Impact3 & "','" & Me.Catagory4 & "','" & Me.Error_type_4 & "',,'" & Me.Impact4 & "','" & Me.Catagory5 & "','" & Me.Error_type_5 & "','" & Me.Impact5 & "')"

CurrentDb.Execute SQL


End Sub



from above code i am not able to get my working done.

Can anybody help me out in this?

Can we use a variable in referring a field in insert query?

I am using variable because i am not able to write the whole code in a single

line and when i am pressing enter it gives me a error.

Please help.... thanks in advance

Regards
Jazz

 

[bob fitz]

Why use code. Just bind the form and its controls to the table and its fields.
BTY. In
Dim a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, aa, bb, cc, dd As String
I think that only dd will be declared as a string. I think the other variables will be declared as variants.

 

[pr2-eugin]

Hello Jazz, Why are you trying to scratch your nose with your elbow? Could you not just bind the Form to the table? Would that not be a easy/better way to do this?

 

[DavidAtWork]

what are the field names in your table [Final Data], you need to refer to those in your INSERT statement

SQL = "Insert into [Final Data](Field1, Field2, .... etc) values ( '" & Me.Month & "','" & Me.Week & "', .... etc)
ensure you list the field names and values in the correct corresponding order and adjust the syntax around the variables to suit the data types ie
for dates:  #" & dates & "#
for text:   """ & text & """ or '" & text & "'
for numbers:   '" + str(numbers) + "'

David

 

[missinglinq]

Using Unbound Forms really does away with the basic function of Access, which is to facilitate RAD (Rapid Application Development) and should only be attempted by very experienced Access developers, and then only when/if a legitimate purpose requires it, and this situation doesn't require it. With Bound Forms Access does the vast majority of the heavy lifting; with Unbound Forms the developer has to write code for everything, even the most mundane tasks.

If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

• You can create an EXE file which gives total protection to your code/design
  
• You can distribute the db to PCs without a copy of Access being on board
  
• Your data security is far, far better than anything you can do in Access

Linq ;0)>