After storing a new record is this record current on form/recordset? 1

[smsmail]

Hello,

I have a form in which the user click the ADD button, the program insert the new record into recordset.

Is the new record, the current record in the recordset/form?

If not, how would I make it the current?

Thank you!
smsmail

 

[MajP]

The answer is no for DAO, I did not test ADO. You need to use a find first. This will prove that it does not become the current record

Public Sub testdate()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("products", dbOpenDynaset)
Debug.Print "Show it is at 1st record: " & rs.AbsolutePosition
rs.AddNew
  rs!ProductName = "New Product"
rs.Update
Debug.Print "Show it not at the new record: " & rs.AbsolutePosition
rs.FindFirst "Productname = 'New Product'"
Debug.Print "Show it is moved to the new record: " & rs.AbsolutePosition
End Sub

Results:
Show it is at 1st record: 0
Show it not at the new record: 0
Show it is moved to the new record: 77

 

[smsmail]

Thank you MajP for your help.

I am using ADO and have found out that the newly saved record does not become the current record.

 

[TheAceMan1]

How are ya smsmail . . .

In perspective of [blue]looking at the form[/blue], the current record is always that indicated by the record pointer. If your talking about hitting the navigation [blue]Add New[/blue] button, then no ... the current record becomes the add new record line.

If you were to have a [blue]save record[/blue] button which saves the record thru VBA then yes ... the current record would remain at that record you just edited.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[smsmail]

Thanks AceMan1 for your reply.

I am somewhat confused currency. What if you update a record thru VBA update sql code. Is that record, current of recordset?

 

[MajP]

No. You could test that to verify. In fact it is unlikely to even exist in the recordset unless you force a requery.

 

[MajP]

I believe you are referring in your post to a user developed "ADD" button that runs vba code on the forms recordset or on the underlying tables. AceMan's first statement is referring to the built in menu "New" record or the |> navigation button.

Bottom line, neither an insert query or vba code "add new" on the forms recordset moves the form's recordset bookmark to the new record.

 

[smsmail]

Thanks MajP

I just saw your reply. I submitted another thread (a little more detailed) before reading your reply.

I am still confused!

 

[MajP]

Ok some of your details really would change the answer and explanation. It appears you are using an unbound form. Is that the case? If so this conversation is irrelevant because an unbound form does not have a recordset, and thus the term current record is meaningless.

1) If this is unbound, is there a reason why?
2) Explain how you populate the form using ADO.
3) Explain how you do navigation.
4) If the code is not too long provide the form code

Explain clearly what you want to do that may be easier to answer, then I will try to explain the why.

 

[smsmail]

MajP ~

I am using ADO. My form is bound. When the form/program initially opens, it opens to a blank form. It opens using the code:

DoCmd.GoToRecord , , acNewRec

The program gives the user the option to add a record, by inputting data in the fields and clicking the add button.

If the user wants to modify or delete a record, the program allows the user to input selection criteria in specific fields on the form, then obtain the record the user wants to modify or delete using the "view" button. My program uses SQL code to obtain the records the user want to modify or delete. I am not using any methods to navigation (i.e. findfirst, movenext, etc) the recordset. I am using SQL.

My questions are:

1. When a record is obtained using SQL code and displayed on the form, is the record obtained, the current record in the recordset?

2. If not, how can I make the record current of the recordset?

3. Also, I want to display the oldvalue of a fields on the form when it has been updated. The oldvalue of the fields currently is NULL. I think this is occurring because the record that is displayed on the form, actually is not current in the recordset. Is this true?

Thanks in advance for your help and giving me some clarity on recordset currency.

smsmail

 

[MajP]

So just to verify you are making an ADO connection to some external database like SQL SERVER and then you are binding the ADO recordset to the Form.

When you say you navigate through sql, exactly how are you doing that? Are you creating a new ADO recordset with one single record? If that is the case then yes it is the current and only record in the recordset.

In regards to the old values (and I am guessing a little because I will have to verify with a bound ADO recordset, which is not the most traditional method), the form has two buffers. There is a control buffer and a form buffer. When you make a change in a control the buffer holds the value until you make the form no longer dirty then the change is committed to the control. The form buffer holds the control changes until the record is saved which commits the values from the control recordset and written to the table.

So in regards to old values, when and how are you trying to display them?

 

[smsmail]

MajP

I am using ACCESS database, not an external one.

Yes I am creating a new ADO recordset with one single record. (It just clicked) Thanks!

Thanks for your patience. Still learning. VBA Access is very different from the mainframe!

smsmail

 

[MajP]

Just out of curiosity, why ADO? If working in a pure Access solution I think you are probably better off in DAO. Most of the native functionality such as the forms recordset is in DAO. ADO is fine and will work (I assume that is your background) but DAO tends to be easier since most access functionality is based on DAO. However you may want to just consider using the traditional method of setting the recordsource to a query/s. you can still navigate this way by assingning the form to a query that returns a single record, changing the recordsource, or moving in the recordset. There are probably a lot of ways to accomplish what you want, and this seems to be a more complicated way. Access' ability to bind to a query or table is really powerful. A lot of coders like yourself with background in web or external apps think they will gain more by making unbound forms or binding the recordsets. Often you give up a lot and end up doing a lot of work that is simpler in a more traditional way.

 

[smsmail]

MajP

I came from a Mainframe background, VBA ACCESS is totally new to me. I went with ADO, because it was heavily suggested by way of my first VBA ACCESS book, I know have three books. As I get more comfortable with VBA ACCESS, I do see that if I had chosen DAO I would have more functionality with recordsets. It has been a learning experience.

However, with help from persons as yourself and this site, even though challenging, I have learned so much.

smsmail

 

[MajP]

ADO was going to cure cancer when it first came out, and there was a big belief that DAO would be deprecated in Access. However, it was found out that for JET specific things DAO was real efficient and works well. ADO is far more robust, and the thing to use when working with external databases.

Out of curiosity what book is suggesting this? Your not the first to say that. I find it interesting when introductory books and courses push the use of unbound forms and binding your own recordsets. These have their place, but are the outliers not the norm, and are more advanced topics. I rarely use unbound data forms and even rarer do what you are trying, and I build lots of apps. I have seen people post on this site where they took an intro course and did nothing but unbound forms. I have to guess the instructor is vb developer who never worked with Access.

A lot of people think you need unbound forms to ensure that data is not saved until the user hits a "Save" button. But you can easily fake this functionality with a bound form.

 

[smsmail]

MajP

Access 2002 Desktop Developer's Handbook suggested "if you are building a new application in Access 2002 , use ADO for your data access needs etc.".

Since you bought the subject up: I was surprised to see that Access prematurely saves a record. All of my forms a BOUND. I did find a temporary work around, but need a better solution before moving the application to production. When closing the form I get a message:
You can't save this record at this time.
Microsoft has encountered an error while trying to save a record, etc.


My present workaround:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    DoCmd.SetWarnings False
    Cancel = True
End Sub

What is your workaround? Can I prevent the message from displaying as the user closes the application?

smsmail

 

[MajP]

I have the book and it is a great resource. I believe that you may have read a little too much into that, though and I think as I said some of the exuberance of Access specific apps may have subsided. I think the point is there is times when you will have to manipulate recordsets through ADO or DAO. ADO is pushed because it is the best library for learning new applications. I do not think they are suggesting to necessarily do this with your native forms.

To fake the saving of a record look at the forms .undo property. As stated before the form has a buffer, and the undo property rolls back to the old values. See pgs 33-34 of your DDH. If you really want to control this, then that is when you may consider a true unbound form. The fake will appear to the user the same.

Without seeing your code, I can not say why your throwing that error; however, See page 484 for form level error handling. Things get very trick when you are working on the forms recordset through the form and at the same time manipulating it with code. Basically it appears as if two users are accessing the same record and causes a conflict.

 

[smsmail]

MajP

I do not understand. Please clarify "native form".

Aren't my native forms, Access forms and I am creating new applications using Access.

I have read information on ADO vs. DAO, while I do not understand it all, I have come to appreciate that DAO works better with Microsoft's jet database engine.

 

[MajP]

I am just suggesting that 95% of the time you can simply set the forms' recordsource to a query, without the need to establish and bind a recordset. "Native" form was a poor choice of words. ADO is fine especially if you are comfortable with it. Unlikely to see performance issues either way. I would not worry about changing anything. But using recordsets in this way is in my opinion is a lot extra work that Access does for you. In my opinion unless you have some compelling reasons to do it this way, I think you could get the same effect much easier using the standard method of setting the forms recordsource to a query. What you are doing should work fine I just do not see any advantage and a lot of extra work. Hopefully someone else can chime in on this with a different opinion.

Also I would think you would lose efficiency by navigating the way you are doing. Instead of moving in the open recordset, you make a new recordset each time. However, there could be some advantages here if the data set was real big. I still bet you could navigate quicker by setting the recordsource sql to just a single record.

 

[smsmail]

MajP,

You have given me a lot to think about!

I agree with you that I need to rethink and redo the way my program is dealing with recordsets. Creating a recordset each time, I need to access it is not efficient.

Actually, I did not know that you could set the form's record source property to a query, but it makes sense. I just did not consider it. If I make that change, it seems as though I would still have to create and open the recordset.

Would I create and open the recordset in the "LOAD" or "OPEN" event?

smsmail