help with coding ADO recordset 1

[Spyridon]

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

 

[cmmrfrds]

I don't understand why you are creating a recordset. Why not base the Form on the Sales table?

 

[Spyridon]

Well, for a few reasons,


This is an extremely scaled down version, in the real database I have a main form with 8 different tab strip options which house there own forms, whose data is either totally unrelated or slightly unrelated to each other. This could be done just by having multiple forms, but, it's easier for people up here just to have one form to navigate with. Right now I have it set up with subforms and quite a few queries, but I'm looking for a better way.

I also want to know, just so that I know how. I've heard quite a few times on here that unbound forms do work somewhat faster, and they provide more flexibility, all good things to have at my disposal if needed.

 

[Quehay]

MoveNext goes to the next existing record and points to nothing if there isn't one there. To create and move to a new record use the AddNew method (again). Unlike DAO the Update will occur automatically when you move, but overt updating is still probably better.

To clarify terms a bit:

create a "new record" occurs in the data abstraction layer(ADO) but there's no new record until you actually insert in the db. Updates only occur to existing records.

I'd be inclined to do this more in straight SQL executed through the active connection. If you're inserting records there's no need to query data from the table and put it into a RST object. Just get form values and insert. The CNN.Execute method will return a value for "records affected" that you can use to confirm the insert.

If you truly need to update existing records you would do well with a Rst object.

Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.InsightDataConsulting.com

 

[Spyridon]

well, I'm kind of at the same place here by using the INSERT, it's getting the data into the table, but the controls hold the same info, and it's not letting me look at the new blank record, if that makes any sense.

For example, user types in "blah blah blah" into the textbox, and then selects choice 2 in the option group, then hits the command button. The table now has that data. but the user can't hit the next record button to start over and do it all again. If he presses the last record navigation button, the data is still the same, in the textbox it is "blah blah blah", and the option is still 2. How can I get the text box to be blank, the option back on the default choice, and be at a new blank record ready for data entry, hopefully without having to hit the command button to activate the code for all of this? Sorry if I sound confudled, but well.

Michael

 

[Quehay]

OK the form controls probably need to be refreshed. Try doing a form.refresh first --I forget if that needs a bound form to work. Otherwise something like:

On Error Resume Next
For each Ctrl in Frm
If Ctrl.Value Then
Ctrl.Value = NULL

End If

Next

Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.InsightDataConsulting.com

 

[Spyridon]

yep, that worked like charm, what I put was this:

textbox.value = ""
and optiongroup.value = 1

I guess the main problem here, is that if a user made a data entry order, they need to be able to go back and edit the record. If the form was bound, that would be easy enough, they would just scroll back, but if it is unbound??
Actually, scratch that. Both ways, if I close the form and then open it back up, it shows me that I have such and such number of records, but it doesn't give me the data for those records.

 

[Quehay]

put a lookup combo on the form with a SQL statement similar to what you've got for the form with ID field the bound column in box. Refresh the box after inserts and updates.

Use the AfterUpdate of box to go to the selected record.

*Watch out for accidental creation of records with zero length string values or the error that occurs if the table column doesn't allow them.

Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.InsightDataConsulting.com