By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

Basic SQL in VBA help

P: 46
Hello,

I am trying to write a command to upload data inputted on a form into a table (I am aware that this is counterintuitive to the table/form relationship).

Here is my code:

Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute "INSERT INTO Table1 ( Salary, Employee ) VALUES '" & [Forms]![Form1]![EmpSalary] & "', '" & [Forms]![Form1]![EmployeeName] & "';"
Suppose I had an additional field in the table, "Hire Date" (format: date/time) and a text box on my form, "Date". How would I include that in the code? I am not familiar with SQL or VBA - trying to teach myself and the apostrophes and quotes are throwing me off - any help you can provide is very much appreciated!
Jan 6 '08 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,623
Hello,

I am trying to write a command to upload data inputted on a form into a table (I am aware that this is counterintuitive to the table/form relationship).

Here is my code:

Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute "INSERT INTO Table1 ( Salary, Employee ) VALUES '" & [Forms]![Form1]![EmpSalary] & "', '" & [Forms]![Form1]![EmployeeName] & "';"
Suppose I had an additional field in the table, "Hire Date" (format: date/time) and a text box on my form, "Date". How would I include that in the code? I am not familiar with SQL or VBA - trying to teach myself and the apostrophes and quotes are throwing me off - any help you can provide is very much appreciated!
Try:
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute "INSERT INTO Table1 (Salary, Employee, [Hire Date]) VALUES('" & [Forms]![Form1]![EmpSalary] & "', '" & [Forms]![Form1]![EmployeeName] & "', #" & [Forms]![Form1]![Date] & "#)"
Jan 6 '08 #2

Expert 100+
P: 374
Hey since you're new to VBA and SQL Statements I'm going to give you a small nugget of information that you'll be able to use in just about anything that you want to do in DAO (Data Access Objects).

If you want to open a form that is bound to a table that you want to be able to set in code and be able to update the table yourself with VBA here is what you're going to need to do.

Expand|Select|Wrap|Line Numbers
  1.  
  2. 'Make sure that you have referenced the Microsoft DAO 3.x Library otherwise this example will not work.
  3. ' To do this, you need to be in the VBA Code editor, click on Tools > References and select Microsoft DAO 3.6 or Micorosoft DAO 3.x of whatever version of MS access you have installed.
  4.  
  5. Private Sub Form_Open(Cancel As Integer)
  6.     Dim MyDB As DAO.Database
  7.     Dim MyRS As DAO.Recordset
  8.  
  9.     Set MyDB = CurrentDb()
  10.     Set MyRS = MyDB.OpenRecordset("SELECT * FROM IMG_TLY_BATCHCOUNTER", dbOpenDynaset)
  11.  
  12.     Set Me.Recordset = MyRS
  13.  
  14. End Sub
  15.  
  16. 'By do the above, this will still allow you to move between Items in the recordset and make and changes to the recordset and even add records to the record set. 
  17. 'If you're wanting to do it totally without even binding the form to a datasource at all, then that when it really starts to get interesting with all of the code that you're going to need to write for every little things that you need to do in order to update, delete, insert, and change records as you're going through each record in the table.
  18.  
  19. 'To edit a record within code the follow DAO example would be valid.
  20.  
  21. Private Sub EditRecord_click()
  22.     Dim MyDB As DAO.Database
  23.     Dim MyRS As DAO.Recordset
  24.  
  25.     Set MyDB = CurrentDb()
  26.     Set MyRS = MyDB.OpenRecordset("SELECT * FROM [Table Name]", dbOpenDynaset)
  27.     'If you want to edit a particular record then you would have to put a Conditional
  28.     'statement in the SQL Statement that goes in the OpenRecordset Method. Example
  29.     ' ("SELECT * FROM [TABLE NAME] WHERE [FieldName] = " & Numeric_Value )or
  30.     ' ("SELECT * FROM [TABLE NAME] WHERE [FieldName] = '" & String_Value & "'")
  31.     ' You don't need the () on the string. just for the method itself is all as I've shown above.
  32.     'Please note that if you want to use values from the form, just simply refer to them by using
  33.     'the Me.obejctname.value to get the value of the object on the current form.
  34.  
  35.     With MyRS
  36.         If Not .EOF Then
  37.             .Edit
  38.             !FieldName = StringOrNumeric_Value 'You can put whatever value go into that field
  39.             !FieldName2 = StringOrNumeric_Value2
  40.             .Update 'This update the current Record
  41.         End If
  42.         .MoveNext 'this allows you to move to the next field
  43.     End With
  44. End Sub
  45.  
  46. Private Sub AddRecord()
  47.     Dim MyDB As DAO.Database
  48.     Dim MyRS As DAO.Recordset
  49.  
  50.     Set MyDB = CurrentDb()
  51.     Set MyRS = MyDB.OpenRecordset("SELECT * FROM [Table Name]", dbOpenDynaset)
  52.  
  53.     With MyRS
  54.         .AddNew
  55.         !FieldName = VariableOrValue 'Example !FullName = me.FirstName.Value & " " & me.LastName.Value
  56.         .Update
  57.     End With
  58.  
  59.     MyRS.Close
  60.     MyDB.Close
  61.  
  62.     Set MyRS = Nothing
  63.     Set MyDB = Nothing
  64.  
  65. End Sub
  66.  
  67. Private Sub DeleteRecord(PrimaryKeyValue As Double)
  68.  
  69.     Dim MyDB As DAO.Database
  70.     Dim MyRS As DAO.Recordset
  71.  
  72.     Set MyDB = CurrentDb()
  73.     Set MyRS = MyDB.OpenRecordset("SELECT * FROM [Table Name] " & _
  74.                                   "WHERE [FieldNameofPrimaryKey] = " & PrimaryKeyValue, dbOpenDynaset)
  75.     'Please make sure that you pass the primary key value to the routine,
  76.     'otherwise it will not find anything to delete and generate an error message.
  77.  
  78.     With MyRS
  79.         If Not .EOF Then
  80.             .Delete
  81.         End If
  82.     End With
  83.  
  84.     MyRS.Close
  85.     MyDB.Close
  86.  
  87.     Set MyRS = Nothing
  88.     Set MyDB = Nothing
  89.  
  90. End Sub
  91.  
  92.  
  93.  
Jan 6 '08 #3

P: 46
Thank you guys very much - the posted code works and the information provided is very helpful. Again, many thanks.
Jan 6 '08 #4

ADezii
Expert 5K+
P: 8,623
One critical point that you may also need to mention is that you cannot assign an Open Recordset to a Form Object in earlier versions of Access.
Jan 6 '08 #5

Expert 100+
P: 374
that is very true. Sorry I didn't mention that.
Jan 6 '08 #6

ADezii
Expert 5K+
P: 8,623
that is very true. Sorry I didn't mention that.
Many notable points in Post #3, many members are not aware of the benefits of assigning a Recordset to a Form. Nice job, PianoMan64.
Jan 6 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.