Loop through records in a temp table before it loads into the form.

[TomKat]

Hello,

I have a query that pulls data from a table based on user input. The data is stored in a temp table. After the user selects the data, could be from 1 to 5 or more, it puts it in a continuous form.

What I would like to have happen is before the data is loaded into the form. loop through each record and present the user with a Yes/No msgbox. Once it reaches the last record, exits out of the loop or "Do while" and goes to the form. The form will then populate the information of the data and change certain fields based on the "Yes/No" answer of the user.

I have this now based on just the click of a button on each continuous form. I am trying to elimate that and have it done before the data is populated into the form.

Button Code:
Dim answer As Integer
answer = MsgBox("Checking in " & Chr(34) & Me.Asset & Chr(34) & "?", vbYesNo + vbQuestion, "Equipment Check In or Out")
Select Case answer
Case vbYes
Me.DateIn = Date
Me.DateOut = ""
Me.Status = "2"
Me.EmployeeID = ""
If Me.Status = "2" Then
answer = MsgBox("Is this a Hot Spare PC?", vbYesNo, "HOT SPARE or NOT")
Select Case answer
Case vbYes
Me.Status = "5"
Case vbNo
Exit Sub
End Select
End If
Case vbNo
Me.DateOut = Date
Me.DateIn = ""
Me.Status = "1"
Me.C420_Location = ""
Exit Do
End Select
-------------------------
The field "Asset" (Me.Asset) is the key field they are using to search and
pull the specific record. I can get it to loop through the records with the
"Do until" but keeps looping and will not exit. I put the above on the "Forms
load" event. see below with Do and Loop statements added.

Dim answer As Integer
Do Until Me.Asset = Null
answer = MsgBox("Checking in " & Chr(34) & Me.Asset & Chr(34) & "?", vbYesNo + vbQuestion, "Equipment Check In or Out")
Select Case answer
Case vbYes
Me.DateIn = Date
Me.DateOut = ""
Me.Status = "2"
Me.EmployeeID = ""
If Me.Status = "2" Then
answer = MsgBox("Is this a Hot Spare PC?", vbYesNo, "HOT SPARE or NOT")
Select Case answer
Case vbYes
Me.Status = "5"
Case vbNo
Exit Sub
End Select
End If
Case vbNo
Me.DateOut = Date
Me.DateIn = ""
Me.Status = "1"
Me.C420_Location = ""
Exit Do
End Select
Loop

Any help would be appreciated.

 

[ajetrumpet]

This is very complicated. I'm not even going to attempt to answer, but if you post your database, one of us may be able to help you out with it...

 

[georgedwilkinson]

What on earth do you think you're going to do with this data? I see no where the data is consumed.

What are you trying to accomplish? Why not just apply a filter to a form or report's recordset? You can use Access built in filters (which doesn't require ANY VB coding so is highly recommended) or you can write your own filtering routine, which consists of creating a SQL statement to act as the record source or where clause (Filter) for an existing record set.

And the reason that your loop won't exit is because nobody ever sets the value of the control called "Asset" to Null, which is impossible to do. And it doesn't make sense that they would do that while a process is running in an infinite loop waiting for data to disappear off of a form.

 

[TomKat]

hmmm I am new to VB and have been learning as I go. Basically, if I did not have the loop. The data is pulled from a query, dumped into a temp table, then displayed on the form. Then the user interacts with the data
changing some of the fields based on the situation (the Y/N part) where
based on their answer, it changes some fields automatically.

I am trying to toss that Y/N part in where by, before it displays it in the
form, to look at each record, give the user the Y/N part, then display the
data on the form already changed and ready to confirm, save and close.

When the form is saved and closed, it updates the changed data to the
main tables.

 

[TomKat]

Oh yes, please keep in mind that what I posted is just an action based on a button the user clicks. The data, per say, is already in a temp table waiting to be viewed, changed and updated. The code is to manipulate changing some of the fields in the temp table based on the Y/N answer.

Maybe that will help some more.

 

[nIGHTmAYOR]

on your head be it

    Dim rst As DAO.Recordset
    
    Set rst = Me.RecordsetClone
    rst.MoveFirst  'if on abort you want to start from an exact record use instead  rst.Bookmark = Me.Bookmark


    Do Until rst.EOF 
        If MsgBox("Do You Realy Wanna Do This ?", vbCritical + vbYesNo, "No Clue") = vbNo Then
            Me.Bookmark = rst.Bookmark
            MsgBox "Aborted"
            'Code while No condition met
            Exit Sub
        Else
            MsgBox "Moving On"
            'Code while Yes condition met
            rst.MoveNext
        End If
    Loop
    Set rst = Nothing

 

[georgedwilkinson]

Tomkat,

This approach is not standard. Just create a form bound to your "main" table and be done with it. There is no reason to use a temp table. Forget VBA. Use Access' built in functionality.

 

[TomKat]

Allright, I will try that. This is the biggest DB I have made or really the second ever. I am learning as I go. So any help is appreciated and this
forum is the greatest.

 

[georgedwilkinson]

Cool, we're always happy to help. But keep it simple. Access was designed to do most of the work for you. If you find yourself writing a lot of code, your first question to us should be: why?

 

[TomKat]

Gotcha....

Let me rephrase on my last. The form is displaying data from two tables.
That is why I had to create the query to have the user enter the criteria
from the specific field to find the record and dump it into the temporary
table. The data is then changed and then the changed fields are updated
to the two main tables.

Bascially, I have a DB that tracks computer equipment that is either in
use, in storage, disposed and what equipment is assigned to each employee. When one of us picks up one of the PCs either taking out
to assign to an employee or bringing it back and putting it on the shelf.
This one main form allows us to pull the specific equipment, change or
update the information and then updates the main tables. One table
is Assets and the other is Employees.

Everything I made is using macros, crosstab queries, etc. It functions fine, but am trying to tweak it more based on input from my fellow techs, thus, me dipping into VB.

My DB now is set up as a front and back end. I would have to piece back
together to zip and attach here. I was hoping that I could put a "Do while" or Loop function in on what I have as I only need to tweak a few more things and it is pretty much done.

 

[Rabbie]

I still think you are missing something. If the form uses data from two tables then build a query to get the data from both tables and build the form on that query.

Are your tables related? Is your data normalised? Many problems we see here are caused by faulty design and a tendency for people to overestimate their coding ability. I look at some of my early designs and shudder and I came to Access with a lot of coding experience. That's how I know the pitfalls.

Finally a word of advice. Code is much easier to read if you put it in code tags. That's code within two square brackets at the start and /code wwithin the rackets at the end.

Good luck

 

[TomKat]

Yes, the form is based on the query that pulls the data from both tables. The query itself has to fields that prompt the tech for input and I have a VB script that loops the query until the tech has pulled all the records they desire. Those records I dumped into a temp table for editing before they are updated back to the main tables. I have a few more fields in the temp table that are updated into a 3rd table. I know it sounds complicated but the 3rd table contains different data from tech input and is for tracking purposes. A must my supervisor requires. If and If I knew alot more VB, then I am sure the two fields in the query that prompt the tech could be written into VB. Thus getting everything into VB. When and if I have the time to start learning, I would probably get it one day. Just trying to find a quick solution for now.

Yes the two tables are related with an ID field. Main table "Assets" has a field that relates to the "Employees" table for a relationship. Each table is unique. I would say my only fault is my naming technique for querys and tables and forms. I did not use the frm, qry, tbl etc.. probably my most #1 downfall along with using # sign and spaces. Easily fixable but a little tedious going back through. Again, second DB I have created and not bad for someone that learned from scratch.

 

[nIGHTmAYOR]

what is your question again ?

 

[TomKat]

Ok, here goes again....

I have a form that displays data from two tables based on a query
that pulls the data together for the form. I have two fields in the
query that prompt the tech for information to search for each record.
I have a VB script that loops the query until the tech has entered
each record they are searching for. Keep in mind that the data has
not been displayed yet on the form until the tech exits the loop by
selecting NO on the popup window.
Then the form is displayed showing all the records selected.

What I have now is a button that the tech clicks on each record. This
form is a continuous form. The button changes certain fields based on
the techs Yes/No input.

My goal is to put the VB built into the buttons on click event, to loop
through each record before displaying the records on the form. This
way, the fields are changed, the tech just reviews each one and hits
the save button.

All this data is put into a temp table before it is updated to the main
two tables.

Is that a better explanation?

 

[nIGHTmAYOR]

what is see is just a setting but not a question .. did u try the code i posted to u erlier , the one that loops and uses bookmarks ?

 

[TomKat]

I need you to explain more on how and where to put what you posted earlier. And do I need to change any text that you posted to match
a field that I have?