Hey if anyone could help here that would be grrrreat!
I'm practicing with ADO on a very simple form. The form is unbound with two controls, a text box, and an option group. The text box is elagantly called, textbox, the option group is likewise called, optiongroup. I'm using ADO to add a new record to a table called Sales, which has two fields, SalesID and SalesType. It's almost working right, except for a few things. It does add a new record when I place the code in an on click event on a command button. But what I'm wanting to do is act more like a regular bound form, in that after adding a new record the user is taken to a blank record that is ready for an update. I did set the recordsource of the form to the Sales table, although that is really NOT what I want to do here. But even when I do that, the controls on the form keep their same value as I navigate through the records. And one little extra thing, how can I make this happen without having a command button to do the work?
So in summary, what I want a user to do is enter something in the textbox, choose an option from the option group, and then click the little arrow button for next record and start all over again... Here's the code that I have so far, thanks again for any help!
Michael
Private Sub Command11_Click()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim opt As String
Dim salesnum As String
Dim text As String
Set cnn = CurrentProject.Connection
opt = [OptionGroup]
Select Case opt
Case 1
salesnum = 1
Case 2
salesnum = 2
Case 3
salesnum = 3
End Select
' Open the recordset
rst.Open "SELECT * FROM Sales", _
cnn, adOpenKeyset, adLockOptimistic
' Add a new record
rst.AddNew
' Specify the values for the fields
rst!SalesID = [Forms]![Main]![textbox]
rst!SalesType = salesnum
' Save the changes you made to the
' current record in the Recordset
rst.Update
rst.MoveNext
'Close the recordset
rst.Close
End Sub
I'm practicing with ADO on a very simple form. The form is unbound with two controls, a text box, and an option group. The text box is elagantly called, textbox, the option group is likewise called, optiongroup. I'm using ADO to add a new record to a table called Sales, which has two fields, SalesID and SalesType. It's almost working right, except for a few things. It does add a new record when I place the code in an on click event on a command button. But what I'm wanting to do is act more like a regular bound form, in that after adding a new record the user is taken to a blank record that is ready for an update. I did set the recordsource of the form to the Sales table, although that is really NOT what I want to do here. But even when I do that, the controls on the form keep their same value as I navigate through the records. And one little extra thing, how can I make this happen without having a command button to do the work?
So in summary, what I want a user to do is enter something in the textbox, choose an option from the option group, and then click the little arrow button for next record and start all over again... Here's the code that I have so far, thanks again for any help!
Michael
Private Sub Command11_Click()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim opt As String
Dim salesnum As String
Dim text As String
Set cnn = CurrentProject.Connection
opt = [OptionGroup]
Select Case opt
Case 1
salesnum = 1
Case 2
salesnum = 2
Case 3
salesnum = 3
End Select
' Open the recordset
rst.Open "SELECT * FROM Sales", _
cnn, adOpenKeyset, adLockOptimistic
' Add a new record
rst.AddNew
' Specify the values for the fields
rst!SalesID = [Forms]![Main]![textbox]
rst!SalesType = salesnum
' Save the changes you made to the
' current record in the Recordset
rst.Update
rst.MoveNext
'Close the recordset
rst.Close
End Sub