Repeat Macro Until Record Count Of Query Equals To 0
Aug 9, 2012
I have a macro that needs to run repeatedly until the number of records in a query =0 but I can't seem to get the Repeat Expression set properly.
My query is called sqMatchCount:
SELECT Count(sqCompare_Parts_Matched_1st.BRP_Entry_Num) AS MatchCount
FROM sqCompare_Parts_Matched_1st;
The theory is if I don't have any more matches then I do not want the macro to run any more. In other words, MatchCount will = 0 when there are no more matches.
I have tried a variety of syntax using DCount in the Repeat Expression:
=DCount("*","sqMatchCount") ...... Macro runs in a continuous loop
DCount("*","sqMatchCount")=0 ...... Macro does not run (although I thought a couple times it actually DID run with this syntax)
DCount("MatchCount","sqMatchCount")=0 ...... Macro does not run
=DCount("MatchCount","sqMatchCount")=0 ....... Macro does not run
=DCount("MatchCount","sqMatchCount") ....... Macro runs in a continuous loop
What IS the proper way to write this Repeat Expression for the RunMacro Action if I want it to run as long as sqMatchCount.MatchCount>0?
Phone No......Bill Date.......Amount _____________________________ 222000........12-02-07.......5689/- Null value.....12-04-07.......2356/- 223223........12-03-07........568/- Null value.....12-05-07.......1256/- 224224........12-01-07.........568/- 225225........12-07-07.........125/- Now my requirement is like this:- ''if any field contains null value then repeat the previous value otherwise keep the original value''. Could anyone solve this problem either by query or macro for access 2003?
Basically, if Calendar date > Date(), if Calendar Date not Saturday or Sunday, weekday(Calendar date<>1 and <>7), AND Calendar Date not in (Holidays table).Then repeat the last not = 0 value of MyValue. I thought of changing the default value but the value is already 0, while default is null + I need to set the default value under certain conditions.
Beginner with Access 2010 - making additions to one field in a JunctionTable. Need to extend a field by adding some new numbers but have found it easier to Export to Excel - make changes and replace in accdb.
I have found an Excel formula that does what I want but starts from the number "1".
=INT((ROW()-1)/9)+1
Which starts with number "1" and repeats this 8 times then increments by one and repeats 8 times.
But I want to start with a number of choice (7270 and go to 9028). Have seen some VBA versions but none give me control of the start number. I am then able to update the second empty field on the JunctionTable from a query. (this is a fixed number).
I'm using Access 2003 to input patient records. I did not set up the database and have limited knowlege of the workings of Access.
My problem:
Normally to repeat a field from the previous record I would use the c>' keys in a blank field and it would be filled with the info that I typed on the preivious record.
However all of a sudden it does not repeat the privious field but one from several years ago.
I have had a look at quite a few threads, but cannot find what i need. I am looking to show a command button if the value typed into the text box equals one of the values in the query. I understand how to show/hide a command button due to another post, but it is getting this to happen IF the text box matches one or more of the values in the query.
I have txtCustNo as the text box to type a number into I have qryCustNo as the query name i have cmdFindCust as command button
Should i have the code under the "after update" part of the text box also?
Is there a way to duplicate, triplicate, a record based on an option group selection. i.e.
Form Field: Option group Value = 1 Value = 2 Value = 3 Field 2: Field 3: Field 4: and so on.
Basically if value 1 is selected, then I fill out the rest of the fields hit enter and go to a new record. But if value 2 or 3 is selected, I fill out the the fields, hit enter. The information is recorded said number of times based on the selection in the option group. Ideally I would have2 or 3 of the same record in the db, with different Primary keys of course.
Is there anyway to reset a query or macro when you open a new record? My database seems to be holding on to data from the previous record. The data is gained from two queries and one macro.
Hello, I have a DB table that only has 12 rows that is continuosly edited throught out the day. What I need to do is log all changes made to the 12 rows throught out the day. I have built two queries (a without match and an append query) which compair the source table with the log table where I store the differences. This works fine but I have to manually trigger the query. I would like to run the query about every 3-4 minutes. Is this possible or is there a better way?
I have a query run that gives me a list of records that I view on a continuos form. What I want is to press a button and run a macro/Append Query to add a Single Summary record to another table.
For example my query spits out this data
Part # Quantity Serial Number GO2 1 123 GO2 2 456 GO2 2 789
What I'm looking to get is
Part Number Total Quantity Serial Number 1 Serial Number 2 .. GO2 5 123 456
I'm stuck on a couple of things.
1. Getting a new single row to append. 2. Getting Serial Numbers from several records to save on to a single record.
Hi, all... welcome back to the new Access World Forums... glad we're still operational, even if some data was lost. It's the minds, anyway, not the data...
I have a query that pulls from 3 transaction tables.
tblTransactions - lists purchaser, amt tendered, other details tblPurchased_Items - lists products and services paid for tblTherapy_Treatments - lists treatments paid for
(Note: these are not separate lists of available items. They're two subforms' tables, one that deals with insurance and one that doesn't.)
I can pull this data fine, but in a case where someone had 1 treatment, but purchased 2 products, the query lists the treatment twice. I do have unique IDs for each table's items, but am not sure how to "list" each transaction once, but each thing paid for once, also.
For example: Joe Blow comes in and has physical therapy and buys 2 books.
My query will show (bogus fields for illustration purposes): TransactionID Name TherapyID TherapyCost ProductID ProductCost 33 Joe Blow 10 180.00 15 12.00 33 Joe Blow 10 180.00 17 19.00
So, if I total TherapyCost it really gives me twice the correct amount.
Can someone point me to a solution? Pardon my rank ignorance, yet again.
The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.
Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?
current SQL SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A FROM [One Year Data Lines] GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));
I have a table with data of people, in which table two fields contain two (not necessarily different) jobs.
I want to build a query (and later a report based on that query) which will return the name, address, phone of the person, plus only the field of the job that I asked for it. when executing the query, the user should enter the asked job in a popup-box using the below formula.
Like "*" & [job] & "*"
Note, that there may be records containing in both job fields the same job. I want to get the field only if it contains the asked job.
I have my Jobs form which has a sub-form for all the different parts used on that job which then calculates the total charge and displays this on the main jobs form. Now how would I get this value to be stored in table tblJobs on the record where the ID = JobID on Jobs form. I need to do this to display the total on a report.
Looked around and couldn't find what I was looking for, is there a visual basic command to set it then I can use docmd.saverecord after or what would I need to do?
Or is there an SQL statement to do this I could execute on a button click or something similar?
I have a form (PostProductionForm) which is used to input data about finished goods which come off a production line. There are three possible outcomes which can occur after the production: Passed, Reprocessed, Failed and the numbers for these are entered in 3 text boxes on the form (QtyReprocessedTxt, QtyFailedTxt, QtyPassedTxt).
These text boxes are auto populated with a 0 from the table (PostProductionTbl) so there aren't blanks.
What I am trying to do is use an IF function (I think) which says that if Quantity Reprocessed is equal to zero when the submit button is pressed, the "Reason for reprocessing" combo (RPReasonCombo) must have something selected (i.e. can't be blank) or it won't go to a new record.
I want to ensure that employees submitted expenses that were actually valid. In table 'Payments', I have the employee ID and amount paid to that employee's account. In table 'Expenses', I have the employee ID and then the individual expenses the employee submitted. How can I create some VBA to find combinations of the employee's records in 'Expenses' that equal the total amount paid for that same Emp ID in table 'Payments'? My ideal end-result would be a report showing each employee IDs with the expenses and then the amount paid beside it. The below is a good start but I need to make it loop through every unique Employee ID.
I have been told that there used to be an option to record a macro in access '97 so that it would record your steps and then this can be saved as a macro and run when needed.
I need to be able to have relationships automatically added between three tables and thought the best way would be to use this record macro option. It doesnt seem to be part of Access now..
does any one know anything about this.. or how I could get Access to assign relationships to the tables automatically?
why =listboxname.column(0) in the control source of a textbox will give me the value "#name?" ? I've used it successfully on another form..but for some reason its not working for me.
I have six fields in a record that look like Median.University1, Median.University2, Median.University3, Median. University4, Median.University5,Median.Unive rsity6. How can I create a calculated field that counts where values equals 4 in the fields . I have tried Dcount and Count with no success.
I really hate being stupid about this, but I'm running around in a circle and it's making me dizzy. All I want is a simple macro that opens a form (with a subform) at a new record from a button. I know I am missing something obvious so I will let others see if they can find out what has gone wrong. The current macro is:
On the property sheet of the form the following are all set to yes: Allow Filters, Allow Edits, Allow Deletions, Allow Additions, Data Entry (I suspect the problem is here) Record Locks is set to No Locks.
When I open the form from the button, it looks right, and I can enter the transaction information, but when I try to enter the subform information or save it , I get "The Microsoft Jet database engine cannot find a record in the table 'NewContacts_tbl' with key matching field(s) 'ContactID'". I get the same message with Allow Data Entry set to no.
And yes the key field IS there, in spite of what the Microsoft Jet database says.
I hope everyone gets a chance to enjoy the holidays and thanks to all of you for your past help and my current (relative) sanity.
I am trying to create a table of help messages and display them help in a form. Using MSAccess.
Each Form used will have a help button, that runs a macro "mcrHelp.xxxxx". This macro should select the relevant record from the table to display in the "Help Form".
I've got a form with a subform on it that has a subform on it (nested subforms - Customers form with computers subform, which has a software licenses subform on it)
I'm trying to put a button on the top-level customers form that will jump to a new record in the software license subform-subform (the licenses form is displayed as continuous with a blank at the end for new license entry). I want to set the OnClick action to move the record selector on the software licenses subform to the last (new) record in the continuous form display. I can't figure out the appropriate path to use as the Form Name in my GotoRecord macro.
Is there a special trick to use when referencing subform controls?
I am basically setting up a macro that will change the value for a field from "N" to "Y" for all records from a query. The macro works fine except for it does not know when to stop and gives an error message of "You can't assign a value to this object" after it has gone through all of the records and there are none left to change. The macro is set up like this:
OpenForm Action : This form is populated by the query and I set the Where condition to open records that don't already have a value of "Y".
SetValue Action : I use this to set the value of the field to "Y"
Close Action : I use this to close the form and save the changes
RunMacro Action : This is where the problem is. I use this to repeat the macro so it can go through the rest of the records that still have the field set to "N". I need to set the Condition or Repeat Expression so that it checks if all of the records have been changed. It either needs to check that all records have the field = "Y" or check that the macro is on the last record. Basically something that will cause the macro to not open itself again when there are no more records to be changed.
