[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access 2007 Looping through control fields

Posted on 2012-09-04
9
Medium Priority
?
450 Views
Last Modified: 2012-09-05
I have 19 fields that contain text "Meets Criteria" or does "Criteria Not Met", each field is based off an if statement determines meets or not met.

My problem is that writing out an if statement below with 19 fields too long.

Here is the code that I'm using now.


If Me.[txtElig-FullTimeStudent].value = "Criteria Not Met" Or Me.[txtElig-Gender].value = "Criteria Not Met" Then
MsgBox "Client is not eligibility to be assigned to program"
'Clear the select PSH provider fields
With Me
.txtSelectedPSH.value = Null
.txtPSHAvailableUnits.value = Null
.txtdatDateofMoveOut.value = Null
.txtdatDateofAvailablity.value = Null
.txtAssignedDate.value = Null
.txtAvailablityDays = Null
End With
Exit Sub
Else
'carry on
End If

I was wondering if its possible to loop through 19 fields and if the criteria is ""Criteria Not Met" then stop the code from proceeding
0
Comment
Question by:jbakestull
  • 5
  • 4
9 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 38365597
dim ctl as control

for each ctl in me.controls
    if ctl.controltype=actextbox then
       if ctl.value="Criteria Not Met" then
            msgbox "message here"

            exit sub
       end if
    end if
next


or you can use the "TAG"  property of the control,
place something like "MET"
then use this codes


dim ctl as control

for each ctl in me.controls
      if ctl.controltype=actextbox then
       if ctl.Tag="MET" then
         if ctl.value="Criteria Not Met" then
            msgbox "message here"

            exit sub
        end if
      end if
    end if
next
0
 

Author Comment

by:jbakestull
ID: 38365657
I trying to use the tag property.

When I place the word "MET"  in the tag field.. (not smart tags)

The code is continuing if one of the text fields contain "Criteria Not Met"
0
 

Author Comment

by:jbakestull
ID: 38365676
Does it matter if the fields are located inside of a control tab?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38365691
post the codes that you are using..
0
 

Author Comment

by:jbakestull
ID: 38365715
Dim ctl As Control


For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
    If ctl.Tag = "MET" Then
    If ctl.value = "Criteria Not Met" Then
        MsgBox "Client is not eligibility to be assigned to program"
With Me
.txtSelectedPSH.value = Null
.txtPSHAvailableUnits.value = Null
.txtdatDateofMoveOut.value = Null
.txtdatDateofAvailablity.value = Null
.txtAssignedDate.value = Null
.txtAvailablityDays = Null
End With
Exit Sub
End If
End If
End If

Next
'carry on

Set conn = CurrentProject.AccessConnection
Set rst = New ADODB.Recordset
If IsNull(Me.txtSelectedPSH) Then
MsgBox "Please select a provider"
Exit Sub
Else
Me.cboReasonReturnedHousingPool.value = Null
'Update PSH Housing Table

'Specify the message to display.
   strMsg = "Are you certain you want to assign client to" & " " & Me.txtSelectedPSH & Chr(10) & vbNewLine
   strMsg = strMsg & "Click Yes to assign or No to discard."
' Display the message box.
   iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Assign Client?")
'Use the ADO connection that Access uses
  ' Check the user's response.
   If iResponse = vbYes Then
rst.Open "SELECT * FROM tblHousing", conn, adOpenDynamic, adLockOptimistic
rst.AddNew
With rst
'!ModifyRecord = fOSUserName
!Client = Me.txtsearch
!EntryExitProviderId = txtSelectedPSH
!DOB = Me.txtDOB
!ClientFirstName = txtLastName
!ClientLastName = txtFristName
!ClientSocSecNoDashed = txtSSN
!MiddleInitial = txtMiddleInitial
!Alais = txtAlais
!Veteran = txtVeteran
!Elibility = cboElibility
!RebulidingLives = cboRebulidingLives
!HousingRank = txtRank
!USHSTotalScore = txtTotalScore
!VulnerabilityScore = txtVulnerabilityScore
!ADAMHScore = txtADAMHScore
!HomelessnessScore = txtHomelessness
!DisabilityScore = txtdisability
!HouseholdStatus = txtHouseholdStatus
!TotalFamilyMembers = txtTotalFamilyMember
!NbrAdults = txtAdults
!NbrChildren = txtChildren
!PSHAssignedDate = txtAssignedDate
!ROIDate = txtROIDate
!ROIExpireDate = datROIExpireDate
!DisabilityDate = datDisabilityDate
!DisabilityExpireDate = datDisabilityExpireDate
!Housingattempts = 1
!Age = txtage
!Gender = txtGender
!Race = txtRace
!Ethnicity = txtEthnicity
!TotalMonthlyIncome = txtMonthlyIncome
!IncomeDate = txtincomedate
!PSHAvailableUnits = txtPSHAvailableUnits
!HouseholdType = txtHouseholdStatus
!Subsidy = cboRebulidingLives
!Eligibility = cboElibility
!Housed = 1
!datDateofMoveOut = txtdatDateofMoveOut
!datDateofAvailablity = txtdatDateofAvailablity
!OldSelectedPSH = txtSelectedPSH
!OldAssignedDate = txtAssignedDate
!OldReasonReturnedHousingPool = cboReasonReturnedHousingPool
!txtFelonyConvict = txtFelonyConvict
!txtFelonyConvictdegree = txtFelonyConvictdegree
!txtFelonyConvictdate = txtFelonyConvictdate
!txtFelonyConvictDays = txtFelonyConvictDays
!txtMisdem = txtMisdem
!txtMisdemdegree = txtMisdemdegree
!txtMisdemdate = txtMisdemdate
!txtMisdemdays = txtMisdemdays
!txtdrugtrafficking = txtdrugtrafficking
!txtdrugtraffdate = txtdrugtraffdate
!txtdrugtraffdays = txtdrugtraffdays
!chkCrimialViolentHistory = chkCrimialViolentHistory
!chkCrimialViolendate = chkCrimialViolendate
!chkCrimialViolendays = chkCrimialViolendays
!cboCriminalproperty = cboCriminalproperty
!cboCriminalpropertydate = cboCriminalpropertydate
!cboCriminalpropertydays = cboCriminalpropertydays
!ManufacturingDrugs = ManufacturingDrugs
!ManufacturingDrugsDate = ManufacturingDrugsDate
!ManufacturingDrugsDays = ManufacturingDrugsDays
rst.Update           '<< moved here
End With
End IF
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38365902
can you upload a copy of your db?
0
 

Author Comment

by:jbakestull
ID: 38367716
Yes, its version 2007, is that ok?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38367761
yes..
0
 

Author Closing Comment

by:jbakestull
ID: 38367776
I figured out what I was doing incorrectly. Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question