Load form with only one record so saves only once

[belovedcej]

I have an unbound form that I want to load with one record. The record is selected based on a selection made in a list box on a previous form.

Then changes can be made to the data.

Then I want to click the save button which runs a stored procedure to update the table with the new data.

I have successfully loaded the form with only the one record. But then when I hit save it tries to update twice and gives me a warning - it saves through the stored procedure and then tries to save again as the form closes. (I think - I get a warning that says another user has changed the record and do I want to overwrite the changes.) I think it's because right now I load it with a view.

Can anyone advise me?

Here is the code that opens the form and gives it the recordsource.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCaseTypeEdit"
stLinkCriteria = Me.listCaseTypes

DoCmd.OpenForm stDocName
 
Form_frmCaseTypeEdit.RecordSource = "Select Case_Type_ID, Case_Type_VC, Case_Type_Description_VC FROM dbo.MED_Case_Type_V WHERE Case_Type_ID = " & stLinkCriteria
                                           
DoCmd.Close acForm, "frmCaseTypes"

Here is the code I use to save with:

Dim Case_Type_ID As Integer
Dim Case_Type_VC As String
Dim Case_Type_Description_VC As String
Dim Deleted_B As Byte
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

Case_Type_ID = Me.intCaseTypeID
Case_Type_VC = Me.txtCaseType
Case_Type_Description_VC = Me.txtCaseTypeDescription
Deleted_B = "0"

With cmd
    .ActiveConnection = CurrentProject.BaseConnectionString
    .CommandType = adCmdStoredProc
    .CommandText = "dbo.MED_UPDATE_Case_Type_T"
    .Parameters.Refresh
    .Parameters(1) = Case_Type_ID
    .Parameters(2) = Case_Type_VC
    .Parameters(3) = Case_Type_Description_VC
    .Parameters(4) = Deleted_B
Set rst = .Execute

End With

DoCmd.OpenForm "frmCaseTypes"
DoCmd.Close acForm, "frmCaseTypeEdit", acSaveNo

 

[belovedcej]

Okay - I have fixed this. Sometimes posting a question triggers the answer! Instead of selecting from the view for the record source, I filled in the fields from the list box.

However, I still have a question. For most of my forms, I will not select the records from a list box - I will have to pull the data from a view in order to load the forms. but then when Access will still try to save the data for me because it will still be bound.

How can I truly unbind a form so that even though there is information in a recordset on the front end, it has no connection to the tables on the back end? I want to run the updates myself, not have Access try to save for me.

 

[tct1954]

belovedcej,

Are the fields on the form bound to the view? If so, you would have to unbind each field and "populate" the fields with code.

tct1954

 

[belovedcej]

That's what I'm not sure how to do.

My form is technically unbound (according to properties) and I have since also unbound all the controls.

But I don't know how to load the information in VBA without also binding it. In otherwords, what I know how to do is to do is put the following on the On Open Event:

MyForm.RecordSource = "Select Statement"

How do I instead:

1. Specify a RecordSet
2. Assign the controls their value
3. Close the RecordSet.

I have tried the following (where the fields are fields in the select statement):

Dim MySQL As Recordset
MySQL = Select Statement

Me.Field1 = Field1   
Me.Field2 = Field2  
etc.

But that's not working.

By the way, I'm somewhat new to the coding thing.

 

[belovedcej]

I think I found something that works. Actually a UK professor's slides.

Dim conn As ADODB.Connection
Dim MyRst As ADODB.Recordset

Set conn = CurrentProject.Connection
Set MyRst = New ADODB.Recordset

MyRst.Open "MED_Cases_V", conn
  
MyRst.MoveFirst
Do Until MyRst.EOF

Me.txtCaseID = MyRst.Fields("Case_ID")
Me.txtCaseName = MyRst.Fields("Case_Name_VC")
Me.txtCaseNumber = MyRst.Fields("Case_Number_VC")
Me.txtCaseShortName = MyRst.Fields("Case_Short_Name_VC")
Me.txtCaseTypeID = MyRst.Fields("Case_Type_ID")
Me.cmbCaseType = MyRst.Fields("Case_Type_ID")
Me.txtDateReceived = MyRst.Fields("Received_DT")
Me.txtNotes = MyRst.Fields("Notes_VC")

MyRst.MoveNext
Loop

MyRst.Close

 

[tct1954]

belovedcej,

The UK Professor's slides is what I had in mind. However I think his code is going to give you errors. At least I get them . Substitute the bang -!- for the periods when referring to fieldnames. Also, I've never used the syntax 'Field("fieldname"). Check this snippet of code to see what I mean.

Me!txtCaseID = MyRst!Case_ID
.
.
MyRst.MoveNext
Loop

tct1954

MyRst.close

 

[belovedcej]

I wasn't getting any errors with the code I had used, but I did try yours as well. It worked the same way.

There is one problem though - It's only loading the first record! There is nothing for me to scroll through.

Is that what is supposed to happen? I was hoping to load the whole recordset.

I suppose I could load the form each time I go want to go to a new record, but it seems like that would take up a lot of processing space.

 

[tct1954]

Belovedcej,

I think using a bound form and controls would be easier and less coding would be required. Setting up a form, binding to a recordsource, navigating through the recordsource one record at a time, adding, deleting, updating and saving records is all rather simple. Easier to maintain also.

Although I have used unbound forms and controls using (DAO)in Access databases (.mdb), I have not using ADO in Access Projects (.adp) therefore I am uncertain of the specifics, since yes, there are differences.

Here is a brief howto on creating bound forms and controls. You probably already know how to do this, but just in case... If you still prefer to code, hopefully someone else can step up to the plate and help.

First - I create a view of the table, sorting or filtering the data as necessary. I do this directly in SQL Server since I am the DBA, but you can do this in Access also if you have the rights on SQL Server.

Next - In the FORM properties dialog box, on the DATA tab, I enter the name of the view in the RECORDSOURCE field. To avoid potential problems with other users not being able to use the form, be certain to prefix the "owner" name to the view, i.e., dbo.viewname. You can use a Select statement here if you prefer.

Next - On the Form Properties dialog, on the Format tab, Navigation buttons field should be set to YES, which is usually the default.

Next - Add controls to the form and bind them to specific fields in the recordsource. You bind the control on the CONTROL SOURCE field, on the DATA tab, of the properties dialog of the control.

You now have a form with navigation that will open showing the first record, with small VCR buttons at the bottom of the form allowing you to navigate. After adding a new record, moving to the next or previous record saves the record. This applies to edits on existing records also.

If you want to add a SAVE button, create an event procedure on the ON CLICK event of the control, then code the following line: "DoCmd.RunCommand acCmdSaveRecord" without the quotes. The SAVE button is not required but can prevent odd time-out errors from popping up occasionally (i.e., when a new record is begun but the end user gets interrupted before finishing. When they return to finish, after entering more fields on the form and going to a new record, a error will occur and the new info is lost).

Hopefully, this was helpful.

 

[belovedcej]

thank you for the advice.

I currently am using bound forms and controls. They are the easy way and I prefer them. I have them set up with my own navigation buttons rather than the default ones, but that was also easy to accomplish.

We set up permissions for the users on the server so they could update, etc. Right now my project works well. I'm just trying to figure out how to do this with unbound forms because that is the standard policy in our department.

But they may make an exception for me since everyone else uses vb.net and I use Access. we'll see!

thanks again for the advice.