****Please be basic as I need to understand this***
I have a vendor table that has x amount of records in it. When I slick a command button i need to go through the record on by one until EOF. Can anyone help me with the syntax on a "do until EOF loop"
Dim dbs As Database
Dim rst As RecordSet
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "Select * from MyTable"
Set rst = dbs.OpenRecordset(strSQL)
While Not rst.EOF and Not rst.BOF
' Do some code ...
rst.MoveNext
Wend
Are you sure that it didn't say "user defined type not supported"?
If it did, it is a reference problem. With your code in design view
select Tools --> References from the menu and make sure that
DAO is checked and promote its priority as high as possible.
If that doesn't fix it, let me know. Or compact/ZIP and post
a copy of your db.
John Doe? (not funny now you edited it from DOA to DAO above!)
I think you mean DAO (Data Access Objects). If you use 2000 or XP I think ADO features higher in the priority list. Circumvent this by explicitly referencing your desired object type.
I tried to reference the specific types and I still get an error. I am getting the user defined type not supported error like you expected. If it help I use access 2000 and I am unable to find DAO in the reference area of the tools menu.
If [status1] = "No Audit" And [status2] = "No Audit" And [status3] = "No Audit" _
And [status4] = "No Audit" And [status5] = "No Audit" Then
Status = "Audit"
Else
Status = "Pass"
End If
Me.Requery
End If
End If
Else
Status = "Audit"
End If
Me.Requery
Forms![vendor number].SetFocus
rst.MoveNext
Wend
'DoCmd.Close acForm, "vendor Number", acSaveYe
End Sub
Ok, lets forget that you are trying to do two things at once here, lets step back a moment and forget about the opening of a second form and trying to relate to controls on the newly opened form remotely and lets also put aside the recordset that you are trying to use and take at look at where you are starting from, a command button On Click event on a form somewhere (this is the only thing that I understand), and what you are wanting as an end result... so what do you want as an end result?
Since you also close the form at the end of the sub, I'm guessing that you don't really want to look at the form you only want to use the form to step through the records and evaluate something.
Ok good you want to evaluate something, ok this is good, now we're getting somewhere.
Next I notice that the form you open is called Vendor and the table you open is also called Vendor and I am going to go out on a limb and guess that the form named [Vendor] is also bound to the table named Vendor, ok, so you want to evaluate data in the Vendor table, please don't keep me in suspense...
Ok, back to the curent form it looks like the curent form is called [Audit Check], ok I see that you have 6 status variable or are they controls on the [Audit Check] form, hmm... maybe they're a text box, ok I'll go with that.
Now is where I get confused, you start your loop and are now getting the [Ven#] value off the [Vendor Number] form, then you get the Stat data from (I think) your 1st (current) form, then you reset your [Status/#] controls, ok I get that.
Now you have several nested If statements... you goto the first record on [Audit Check] and get [Status1], then move to the next record and get [Status2] and so forth through [Status5] determining whether [Status] is and "Audit", "Pass","Fail", and "No Audit", Ok I think I follow that.
Ok now we're at the end of the loop and we are moving to the next record in the recoredset and...
Hmmm... ok below is your cleaned up code as I understand your proccess, I removed the code referencing the second form [Vendor] and used only the recoredset to do... what?
Code:
Dim intStat As Integer
Dim intVen As Integer
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "Select [Vendor#] from [Vendor]"
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.BOF And rst.EOF Then GoTo 50
rst.MoveFirst
Do While Not rst.EOF
[COLOR=green]'I still have a question on these,
'are they varibles or controls on the current form?[/color][COLOR=red]
Status = "None"
status1 = ""
status2 = ""
status3 = ""
status4 = ""
status5 = ""[/color]
intVen = rst![Vendor#]
[COLOR=green]'I assume Forms![Audit check] is the current form
'if not, what is it?[/color]
intStat = Forms![Audit check]![Audit Filter]![txtCount]
If intStat > 2 Then
[COLOR=green]'the followng "acFirst" will move to the first record in the
'current form for EVERY iteration of the loop, therefore
'only the first five records will ever be evaluated[/color]
DoCmd.GoToRecord , , [COLOR=red]acFirst[/color]
[status1] = Forms![Audit check]![Audit Filter]![Pass/Fail]
DoCmd.GoToRecord , , acNext
[status2] = Forms![Audit check]![Audit Filter]![Pass/Fail]
DoCmd.GoToRecord , , acNext
[status3] = Forms![Audit check]![Audit Filter]![Pass/Fail]
If [status1] = "Fail" Or [status2] = "fail" Or [status3] = "fail" Then
Status = "Audit"
Else
Status = "Pass"
If intStat > 3 Then
DoCmd.GoToRecord , , acNext
[status4] = Forms![Audit check]![Audit Filter]![Pass/Fail]
DoCmd.GoToRecord , , acNext
[status5] = Forms![Audit check]![Audit Filter]![Pass/Fail]
If [status1] = "No Audit" And [status2] = "No Audit" _
And [status3] = "No Audit" And [status4] = "No Audit" _
And [status5] = "No Audit" Then
Status = "Audit"
Else
Status = "Pass"
End If
End If
End If
Else
Status = "Audit"
End If
[COLOR=green]'this indexes through the recordset and is correct
'but for what? nothing is evaluated from the recordset[/color]
[COLOR=red]rst.MoveNext[/color]
Loop
50:
Your [Vendor#] recordset doesn't do anything except cycle through records within the loop, it doesn't evaluate anything nor does it get evaluated against anything, I think your process is incomplete.
Also because of the line "DoCmd.GoToRecord , , acFirst" inside the loop just inside the first "IF" statment, with each Loop your current form [Audit Check] will only evaluate the first three or five records and gets sent back to the first recored on the form with every iteration of the loop. Are you sure this is right?
I think this process (when we get it figured out) can probably be simplified/sped up/optimized to cycle through two recordsets simultaniously to do the same thing instead of cycling through a form and a recordset, cycling through the form really slows this down.
Hmmm... lots of questions still and lots to think about, please get back to me if I can be of more help (if this has been any help at all).
To be totaly honest I am sefl taught VBA person who knows enought to make himself dangerous.
The DB is to track audits on a Caselevel receiving in a warehouse.
Every shipment that comes in recieves a "pass", "fail" or "no audit" grade. A vendor must have three consequtive passes to be a caselevel vendor. Case level vendors are audit every fifth "No audit" to ensure quality. Any fails and they start the proccess over. Each vendor is assigned a status of either Pass(pass should be CaseLevel) or Audit.
Everynight we want to run "End of Day"( a command button on the switchboard). This will shuffle through all vendors and their shipments and update their status based on there last 3 to 5 grades.
Answers to your questions.
The form vendor number does nothing, I should connect to the table but I was unable to get that to work.
Status(1-5) are text boxes that should be varibles. I do not need to see them.
Yes I do only want to eveluate the last three to five records.
stat is equaly to the quanity of records in the subform. If there are less than three then status is Audit.
Great , you know what you have, you know what you want it to become and you've recognized the things that need to be changed.
This is very helpful, and I think we can figure out a solution but I won't have time to review your project until tommorrow or sunday.
Ok, I'm back, sorry it took so long to get back to you. I spent a couple hours sorting out your problems they were deeper than I think you know of. Any way I will list out major changes below but I strongly engourage you to review ALL code relating to the newly inserted basAudit module and the code changes on the Audit Check form.
The following corrections/changes were made but are not limited to this list.
- Audit Check form did not have Master and Child links with subform, there for subform would not show related data (set master and child links = [Vendor#]).
- on Audit Check form I cut out the code under the cmdCycle button and pasted it into a new Module called basAudit.
- on the switchboard "End of Day" command button I inserted code to call the fcnAudit function in the basAudit module
- on the Audit Check form I renamed the cmdCycle button to cmdAudit with code that calls the public fcnAudit function in the basAudit module.
- In the fcnAudit function I eliminated all use and reference to both the Audit Check and Vendor forms and replaced them with two recordsets that replicate all desired functions more efficently.
- I commented out all the old code but left in in the function next to rst functions that replaced it. I suggest you look closely at the new code to understand how it works between the two recordsets. When you understand the process then delete the bad code out just to clean it up so it isn't confusing.
- I also added a combo box on the Audit Check form so you could look up vendors and have the form load the selected vendor.
- I changed the Vendor # and Shipment #'s to number fields in the table instead of text fields, and subsequently had to correct the relationships. I don't know how you create a new vendor or shipment number but I suggerst you change the current format to an AutoNumber on both accounts and use a foreign key in the shipment table for the Vendor # instead of two primary keys.
Kevin thanks fo the help I learn so much when I create something and then someone else mends it and I get to view the changes. This DB will be a good source for a long time.
I am assuming that the status update button on the audit check form can be deleted and I get an audit error if a vendor has 4 records.
Also Vendor numbers and shipment numbers is something that is created by our AS400 and they are unique.
In the fcnAudit function comment out the first line "On Error Goto 50" and put in a break point at the begining of the function and run it and then step through it line by line until you get the error and post the line that is causing the error and I will give it another look.
(not funny now you edited it from DOA to DAO above!)
Wow..., I'm not... hmmm..., well...
