[ Pobierz całość w formacie PDF ]
.The uf_NewRecord function sets the form up to enter a new record.The followingcode snippet shows the statements used to clear the form s fields. Iterate through controls on form that match fields inrecordsetFor Each ctl In frm if error the field is not on the formOn Error Resume NextErr = 0vartemp = rst.Fields(ctl.Name).NameIf Err = 0 ThenOn Error GoTo ErrorHandler if control enables then set default value from tag and set focus if tab index 0If ctl.Enabled ThenIf IsNull(ctl.Tag) Thenctl.Value = NullElsectl.Value = ctl.TagEnd IfIf ctl.TabIndex = 0 Then ctl.SetFocusEnd IfEnd IfNext 3596-X ch34.F 5/30/01 11:49 AM Page 1174Part VI &' Access and the External World1174The code in the uf_NewRecord function loops through each field on the form.Eachfield s value property is set to Null.Then, the focus is set to the first field in thetab order.CautionBefore clearing the fields on the form, check to see if the user changed any of thedata.If the data has changed, prompt the user to save or undo the changes.You can use the uf_SaveRecord function to save the data on the form.Use the Savebutton s On Click event to call the uf_SaveRecord function.The first task of the uf_SaveRecord function is to locate the record in the table.Thissection of code is illustrated in the following code snippet: Check to see if data has been changedIf Not frm.FlagEdited ThenMsgBox Nothing to save GoTo DoneEnd If Open connectioncnn.Open frm.Controls( xProvider ) &frm.Controls( xDataSource ) Open recordset to determine type of key field and setupCriteriarst.Open Select * From & frm.Controls( xRecordset ),cnn, adOpenStaticSelect Case rst(frm.Controls( xKey ).Value).TypeCase adChar, adVarWChar, adLongVarWCharstrCriteria = frm.Controls( xKey ) & = &Chr(39) & frm.Controls(frm.Controls( xKey )).Value & Chr(34)Case adDatestrCriteria = frm.Controls( xKey ) & = & # &frm.Controls(frm.Controls( xKey )).Value & # Case Else assume numericstrCriteria = frm.Controls( xKey ) & = &str(frm.Controls(frm.Controls( xKey )).Value)End Selectrst.Close Determine if this is a new record or a changed recordrst.Open Select * From & frm.Controls( xRecordset ) & WHERE & strCriteria, cnn, adOpenKeyset, adLockOptimisticThe function first checks to see if the form has been edited.If not, the functionsimply exits.Otherwise, it opens a connection to the recordset simply to deter-mine the key field s data type so a criteria string can be created.The criteria stringis used to search the recordset for a record with the same key field.This is neces-sary so the function can determine whether to update an existing record or createa new record. 3596-X ch34.F 5/30/01 11:49 AM Page 1175Chapter 34 &' Working with Access Projects1175If it is a new record, the function uses the AddNew method and iterates through thecontrols on the form that match the fields in the recordset to create a record withthe new data.The UpdateCtr field is set to 1 for the new record.If an existing record has the same key field, then the code must check to make surethat the record has not been changed.The code to perform the check is shownbelow: Check to see if this record was already updated by another userIf rst( UpdateCtr ) frm.Controls( UpdateCtr ).Value ThenResponse = MsgBox( This record was already updated by another user. & vbCrLf & _ Do you want to overwrite the other user s changes? , _vbInformation + vbYesNo, Data already changed )If Response = vbNo Thenrst.CloseExit FunctionEnd IfEnd IfThe UpdateCtr field in the form is compared to the value of the UpdateCtr field inthe table.If the two values are different, then the record was updated in betweenthe time that the record was displayed on the form and the time that the userpressed the Save button.A message prompts the user to go ahead and overwritethe record in the table with the user s changes, or to cancel the user s update.To update the existing record, the values in the form s fields are copied to therecordset s field and the recordset is updated.The code to update the recordset isshown below: Iterate through controls on form that match fields inrecordsetFor Each ctl In frm if error the field is not on the formOn Error Resume NextErr = 0vartemp = rst.Fields(ctl.Name).NameIf Err = 0 ThenOn Error GoTo ErrorHandler if control enabled then if it is not an auto increment field if data is not null or an empty stringIf ctl.Enabled ThenIf Notrst.Fields(ctl.Name).Properties( IsAutoIncrement ) ThenIf Not IsNull(ctl.Value) And Notctl.Value = Thenvartemp = ctl.Valuerst(ctl.Name).Value = vartempEnd IfEnd If 3596-X ch34.F 5/30/01 11:49 AM Page 1176Part VI &' Access and the External World1176End IfEnd IfNext Increment the Update Counterrst( UpdateCtr ) = rst( UpdateCtr ) + 1 Update the recordsetrst.Updaterst.CloseThe fields are also checked to see if the field is an Auto Increment type andwhether it is enabled.If it is an Auto Increment type, the field value is notupdated.The UpdateCtr is incremented by 1.A message box displays if the record has been successfully saved, and theFlagEdited field is reset.Finding a recordYou can find records with the function uf_FindRecord.This function can find arecord based on criteria entered in any field on the form.The function checks thevalue of flagFind field on the form.If this flag is true, then the form contains the cri-teria to do the find.If the flag is false, then the form is cleared so the user can enterthe criteria.A message displays, telling the user to enter the criteria and to pressthe Find button again to retrieve the records.You can set the default value of this flag to True so that when the form is opened,the user can start entering criteria right away.After the function completes, theresulting criteria is stored in the Form s filter property for later use.The functiondoes a check to see if the current record has been saved before clearing the formout for the criteria.The heart of the operation of this function is to create a criteria string.Criteria canbe specified for any combination of fields on the form.The function creates a crite-ria string with an AND between all of the selected fields.For example, in our sam-ple application you can enter a state, and all records for that state are retrieved.You can search for a customer number or a phone number as well.The code to cre-ate the criteria string is shown below: Iterate through controls on form that match fields inrecordsetstrCriteria = For Each ctl In frm if error the field is not on the formOn Error Resume NextErr = 0vartemp = rst.Fields(ctl.Name).NameIf Err = 0 ThenOn Error GoTo ErrorHandlerIf ctl.Enabled ThenIf Not IsNull(ctl.Value) And Not ctl.Value = 3596-X ch34
[ Pobierz całość w formacie PDF ]