Recordset updating automatically (1 Viewer)

[nirmal]

Private Sub cmdCreateVisitOrViewAddHistory_Click()
' Open a recordset for the Visit_tbl table
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim ID As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("Visit_tbl", dbOpenDynaset, dbSeeChanges)
ID = Me.txtPatientID.value
Debug.Print "ID value: " & ID
' Add a new record to the recordset
rst.AddNew
rst("PatientID_FK").value = ID
' Set other fields of the Visit_tbl record as needed

' Save the new record
rst.Update

' Close the recordset and release the database object
rst.Close
Set rst = Nothing
Set db = Nothing

' Open the Visit_frm form with a filter for the related PatientID_FK record
DoCmd.OpenForm "Visit_frm", , , "PatientID_FK=" & ID, acFormEdit, acWindowNormal
End Sub

This is a click event on main form. After it opens the linked second form, the Visit_tbl saves the data automatically. I dont want to save it until I click the save button on the Visit_frm.
Help please

 

[Gasman]

So open the form acNormal ?
And please use code tags when posting code.

 

[CJ_London]

the Visit_tbl saves the data automatically.

That is normal behaviour for a bound form. If you do not want that to happen use an unbound form

 

[nirmal]

That is normal behaviour for a bound form. If you do not want that to happen use an unbound form

This is my unbound form. So then why this automatic table is getting updated

 

[nirmal]

So open the form acNormal ?
And please use code tags when posting code.

Sir I didn't get what you want to say

 

[Gasman]

Sir I didn't get what you want to say

I was saying try opening the form with acNormal. I cannot see that form becoming dirty on just opening, unless you have code to dirty it, and now you say form is unbound.
Code tags is to keep indentation in the code and make it easier to read.

 

[nirmal]

I was saying try opening the form with acNormal. I cannot see that form becoming dirty on just opening, unless you have code to dirty it, and now you say form is unbound.
Code tags is to keep indentation in the code and make it easier to read.

Sir, I tried opening the form with acNormal, but the issue persists. I am sending the vba code of the Visit_frm

Private Sub Form_Load()
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Visit_tbl", dbOpenDynaset, dbSeeChanges)

    If Not (rst.EOF Or rst.BOF) Then
        rst.MoveLast
        RefreshData
    End If
End Sub

Private Sub RefreshData()
    If Not (rst.EOF Or rst.BOF) Then
        Me.txtVisitID = rst!VisitID
        Me.txtVisitDate = rst!VisitDate
        Me.cboPatientID_FK = rst!PatientID_FK
        Me.cboReferredByDoctorID_FK = rst!ReferredByDoctorID_FK
        Me.cboExaminedByDoctorID_FK = rst!ExaminedByDoctorID_FK
        Me.txtConsultationFees = rst!ConsultationFees
        Me.txtUsgFees = rst!UsgFees
    Else
        ' Clear the controls when no records are found
        Me.txtVisitID = ""
'        Me.txtVisitDate = ""                      This line commented as the visit_tbl VisitDate default property has been set to =Now()
'        Me.cboPatientID_FK = ""              This line commented as I want to fetch the PatientName from the main table having PatientID
        Me.cboReferredByDoctorID_FK = ""
        Me.cboExaminedByDoctorID_FK = ""
        Me.txtConsultationFees = ""
        Me.txtUsgFees = ""
    End If
End Sub

Now tell me Sir where I am making mistake

 

[Gasman]

Not even going to try and read that without code tags.

 

[nirmal]

Not even going to try and read that without code tags.

Sir I don't know about code tag. Guide me.

 

[nirmal]

Not even going to try and read that without code tags.

Private Sub cmdCreateVisitOrViewAddHistory_Click()
    ' Open a recordset for the Visit_tbl table
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim ID As Integer
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Visit_tbl", dbOpenDynaset, dbSeeChanges)
    ID = Me.txtPatientID.value
    Debug.Print "ID value: " & ID
    ' Add a new record to the recordset
    rst.AddNew
    rst("PatientID_FK").value = ID
    ' Set other fields of the Visit_tbl record as needed

    ' Save the new record
    rst.Update

    ' Close the recordset and release the database object
    rst.Close
    Set rst = Nothing
    Set db = Nothing

    ' Open the Visit_frm form with a filter for the related PatientID_FK record
    DoCmd.OpenForm "Visit_frm", acNormal, , "PatientID_FK=" & ID, acFormEdit, acWindowNormal
End Sub

 

[gemma-the-husky]

If you are using an unbound form, then you will need to explicitly run a statement to update your tables. If this isn't working correctly then your code must not be correct.

Eg. Maybe you are issuing the update instruction as part of a lostfocus event as well as the buttonclick.

 

[Gasman]

Is rst even available to RefreshData?

 

[mike60smart]

Sir I don't know about code tag. Guide me.

What Gasman is trying to say is that when you reply and you need to send Code Then the Code itself must
Have Code Tags before and After the Code.

Before you paste the code into your reply window, click on the icon on the Toolbar for "Code" </>
This will then PopUp a window as shown.
Paste your Code in this window and then click Continue

 

[jdraw]

Maybe it's time to post your database or a copy that exhibits this behaviour along with instructions to see this issue directly.

 

[Pat Hartman]

Access is a RAD (Rapid Application Development) tool. The primary reason for using Access is to take advantage of the RAD tools. The MOST IMPORTANT of those RAD tools is bound forms. By avoiding bound forms, you have cleverly avoided the most useful tool in the box. Do you have a particular reason? Usually people who have never used Access before, think that they know better and so rather than working with Access, they feel the need to "take charge" and "do it themselves" because "they know better".

In reality, it is pretty easy to get Access to be helpful once you understand how form level events work. They are where you would need to place code in order to control whether a record gets saved or not. The most important event for this purpose is the form's BeforeUpdate event. It is the LAST event that fires before a record gets saved and there is NO way to stop it. Therefore, you put your validation code there and if a record is invalid for some reason, you prevent Access from saving the record by using -

Cancel = True

That's it. Think of the Form's BeforeUpdate event as the flapper at the bottom of a funnel. If the flapper is open, the record gets saved. If the flapper is closed (Cancel = True), the record does not get saved. You could also remove all the changes to the record (Me.Undo) but that is pretty Draconian and is rarely necessary. I would only ever use Me.Undo if the user were not authorized to make ANY changes to the record or if I asked the user if he wanted to cancel and he said yes. You are very unlikely to come across code that asks this question in one of my apps because it trains the user to just click yes and to basically stop reading any prompt you make. If you prompt for every save, the users will NEVER read any of your prompts, EVER. They will just always click yes.

 

[bastanu]

@nirmal: I agree with Pat that using unbound forms is usually unnecessary. And I believe yours is not unbound otherwise your Docmd.OpenForm statement in your very first post would not work (you cannot open a form filtered using the Where clause of the OpenForm method unless the form is bound).

I would also point out a few issues with your code. I would recommend to declare the ID variable as Long instead of Integer (unless you will never have more than 32,767 patients).

In the Form_Load event the use of rst.MoveLast could be unpredictable because the recordset is opened on the whole table without specifying a sort order; if you potentially have more than one record you should modify the Set rst=db.OpenRecordset line to include the sort order on the desired field (like a date or ID).

And finally, in your RefreshData() sub you are setting the values of the controls to "" (zero length string or ZLS) which will be saved you the table (because we know the form is bound) and now you will end up with a mix of "" and Nulls in some of the fields. Make them Null instead but again realize that you are actually editing a record in the database due to the form being bound.

In the rare case you truly need to work with an unbound form you need to modify the OpenForm statement to remove the Where clause and pass the ID as the OpenArgs parameter to the Visit_frm and use that in its load event to populate it by filtering the recordset on the OpenArgs ID.

Cheers,