Using Access 2010 I have a client form with unbound fields where data is added manually. I have created a VBA subroutine that adds the data in these fields to a recordset and it works fine except for 2 field types: a date field and a combobox field.
What exactly is the code that fails? The code you've shown here is not updating field data in a recordset.
In addition, a ComboBox is a user interface element exposed by Access, and not really a data type in the sense that you could assign "ComboBox" data to a specifically typed field in a recordset. What is the datatype of the table field to which your combobox data is being assigned? That is the datatype you are in fact dealing with. A combobox can display many different datatypes.
In VBA, some data types have distinct delimiters that distinguish the delimited content as being of specific type. These are double-quotes (") for string data, and octothorpes, (or the number sign) (#), for date data. So in VBA ...
Code:
[SIZE="1"]Private Sub Test10923857409274()
Dim d1 As Date
Dim s1 As String
Dim n1 As Single
d1 = #12/14/2012#
s1 = "12/14/12"
n1 = 12 / 14 / 12
Debug.Print TypeName(d1), TypeName(s1), TypeName(n1)
Debug.Print d1, s1, n1
End Sub[/SIZE]
You want to keep the symptoms to yourself and keep us guessing? When asking for help, always state what you expected, what you did, what you got, text of any error message that appeared, and on which line. "Incorrect" or "doesn't work" does no cut it. Our ESP sux
Okay - Starting over. I am using Access 2010 and am trying to use VBA to create a new record in a recordset and populate it with data from unbound fields on a client(not web) form when I click on a button named Post.
This is my post routine:
Private Sub cmdPost_Click()
Dim rstPayments As Recordset
Dim strSQL As String
'Create a new record in the InvoicePayments table
strSQL = "SELECT * FROM InvoicePayments"
Set rstPayments = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rstPayments.AddNew
'Update all of the payment fields
rstPayments("Company").Value = Company
rstPayments("Date Received").Value = [Date Received]
rstPayments("Date Posted").Value = [Date Posted]
'rstPayments("Type").Column(0).value= Type.Column(0).value <----- This is one
'Clear the unbound payment fields
Company = ""
[Date Received] = ""
[Date Posted] = ""
'Type = "" <----- Commented out
Description = " "
Amount = 0
Applied = 0
AppliedTo = ""
End Sub
The statement with the arrow pointing to it and indicating that This is the one causes
a message box saying "Compile error: Missing expression" when I remove the comment apostrophe. What is wrong with the statement?
Second, you are assigning a value to a Field object in a recordset, and that object does not have a Column property, so when you fix that first problem it will fail here next. Amend something like ...
Code:
rstPayments("Type") = Me.Type.Column(0)
... and since Value is the default property of a Field you can omit it from your code, and a Column(x) in a Combobox or Listbox is a string, and will not have a Value property either.
In addition, code tags make it easier for others to see and understand your code, since understanding someone else's code is not trivial. If you highlight text in the "Reply to Thread" window, hit the octothorpe (#) and the highlighted text is offset and your indents are preserved. Consider ...
Code:
[SIZE="1"]Private Sub cmdPost_Click()
Dim rst As Recordset
[COLOR="Green"] 'Update the [InvoicesWithBalances subform] fields[/COLOR]
With Forms!Invoice_Selected![InvoicesWithBalances subform].Form
!AmountApplied = !AmountApplied + Applied
End With
[COLOR="Green"] 'Create a new record in the InvoicePayments table[/COLOR]
Set rst = CurrentDb.OpenRecordset("SELECT * FROM InvoicePayments", dbOpenDynaset)
With rst
.AddNew
!Company = Company
![Date Received] = [Date Received]
![Date Posted] = [Date Posted]
!Type = Me.Type
!Description = Description
!Amount = Amount
!PreApplied = PreApplied
!Applied = Applied
!AppliedTo = AppliedTo
.Update
.Close
End With
ClearUnboundFields
End Sub
Private Sub ClearUnboundFields()
[COLOR="Green"]' Other process might want to do this too, so create a subroutine[/COLOR]
Company = ""
[Date Received] = ""
[Date Posted] = ""
Me.Type = ""
Description = " "
Amount = 0
Applied = 0
AppliedTo = ""
End Sub[/SIZE]
... as an example of what your code could look like.
If you provide a clear and complete description of the problem, with specific indications of what goes wrong and where, you vastly improve your chance of getting a useful answer.
Thanks for the help. I implemented the changes you suggested and they produced the desired results. Your programming style was also of benefit in bringing an old software developer up to date. The last program that I wrote was in 2007 on Access 2003.
