Update Yes/No fld with DAO? 
Author Message
 Update Yes/No fld with DAO?

Hey,

I have a form with a subform. Form name: frmInquireFamilyMember,
recordsource: unbound; Subform name: frmSubformInquireFamilyMembers,
recordsource: SELECT * FROM tblFamilyMembers_AM WHERE
((tblFamilyMembers_AM.fldDonorID) = ([Forms]![lookup]![txtDonorID])).

The subform lets you fill in family member names and click save on
frmInquireFamilyMember. When you click save I want to use DAO to create a
recordset that Counts the number of names in tblFamilyMembers_AM WHERE
fldDonorID = Forms!lookup!txtDonorID and another recordset that  Selects
fldFamilyPlan From tblMasterSystem Where fldDonorID =
Forms!lookup!txtDonorID. fldFamilyPlan is a Yes/No field.  The result will
be that if the Count > 0 then tblMasterSystem.fldFamilyPlan = True for the
corresponding record.

HOWEVER, my code below does not work. I am missing something basic I am
sure. Help! TIA.

Private Sub Command30_Click()
Dim myws As Workspace
Dim MyDB As DATABASE
Dim myrs As Recordset
Dim mystring As String

On Error Resume Next
DoCmd.SetWarnings False
Set myws = DBEngine.Workspaces(0)
Set MyDB = CurrentDb()

mystring = "SELECT DISTINCTROW tblFamilyMembers_AM.fldDonorID,
Count(tblFamilyMembers_AM.fldFamilyLastName) AS CountOffldFamilyLastName
FROM tblFamilyMembers_AM GROUP BY tblFamilyMembers_AM.fldDonorID HAVING
(((tblFamilyMembers_AM.fldDonorID) = [Forms]![lookup]![txtDonorID]))"

Set myrs = MyDB.OpenRecordset(mystring, DB_OPEN_DYNASET)
myrs.MoveFirst
myrs.MoveLast

If myrs.RecordCount > 0 Then
myrs.Close
mystring = "SELECT DISTINCTROW tblMasterSystem.fldDonorID,
tblMasterSystem.fldFamilyPlan FROM tblMasterSystem WHERE
(((tblMasterSystem.fldDonorID)=[Forms]![lookup]![fldDonorID]));"
Set myrs = MyDB.OpenRecordset(mystring, DB_OPEN_DYNASET)
myrs.MoveFirst
myrs.MoveLast
myrs.Edit
myrs!fldFamilyPlan = True
myrs.UPDATE
myrs.MoveNext
MsgBox "Thank you. Record saved."

Else

mystring = "SELECT DISTINCTROW tblMasterSystem.fldDonorID,
tblMasterSystem.fldFamilyPlan FROM tblMasterSystem WHERE
(((tblMasterSystem.fldDonorID)=[Forms]![lookup]![fldDonorID]));"
myrs.Close
Set myrs = MyDB.OpenRecordset(mystring, DB_OPEN_DYNASET)
myrs.MoveFirst
myrs.MoveLast
myrs.Edit
myrs!fldFamilyPlan = False
myrs.UPDATE
myrs.MoveNext
MsgBox "Nothing to save."
End If
myrs.UPDATE
myrs.Edit
myrs.Close
End Sub

--
S.E. Hanley

Applications Programmer
Callaway Gardens
Pine Mtn. GA



Sat, 22 Jul 2000 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. How to avoid single quotes present in fld name while updating a record

2. DAO 3.5 and multithreading --yes or no?

3. Need help with yes/no Data types in DAO

4. Update Query Always Prompts for Yes or No

5. Need help parsing Memo Fld

6. Divide comma separated FLD into Records

7. Help needed returning fld in snapshot to a table

8. Keeping form open if fld isnull!

9. fld.Required property and bound controls

10. Divide comma separated FLD into Records

11. validating at the control/fld level

 

 
Powered by phpBB® Forum Software