:mad: I have created a database to hold information on non conformances we have with our suppliers, I am really stuck, what I am looking for is to put a field into a table which counts issues with the different suppliers, if anyone can help it would be much appreciated! Thanks
Hi, Im trying to run a query, field name is [Value]. I have yes or no boxes in this field, I set the sum to count and under criteria i have put yes, no, -1, and 0 and they will not correctly add up. When i try yes it counts all of my records. Any help would be great. Thanks.
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 know this is very simple but I am unable to get it work.
I have a Form (based on Table "Trades") and on the Form is a Command button. The Command Button has a VBA Code which selects the items from the Table and coverts to a text file - this works fine but the problem is that I am unable to repeat the process for all records in the table. I have tried using Dim as Intergers and upto a number of records, but it does all records as the first record.
I also tried this:
Dim db as Database set db as currentDb
- but it does not like it!
I know I have to set it as Dim rs as Recordset
but when I set rs as CurrentDb.Recordsets it does not like it!!
I have a little problem and do require help in fixing it. I am only 3% away from finishing the database (due today) but have a little bit of a problem on my hands. here it is:
I have a counter on my form that tracks which record is currently being viewed. the code for the counter is:
------counter code--------- 'Provide a record counter for using with ' custom navigation buttons (when not using ' Access built in navigation)
Dim rst1 As DAO.Recordset Dim lngCount As Long
Set rst1 = Me.RecordsetClone
With rst1 .MoveFirst .MoveLast lngCount = .RecordCount
End With
'Show the result of the record count in the text box (txtRecordNo) If Me.CurrentRecord = lngCount Then Command126.Enabled = False Command129.Enabled = False Else Command126.Enabled = True Command129.Enabled = True End If Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngCount & " record(s) for " & [CLIENT PREFIX] Me.Text292 = "Record " & Me.CurrentRecord & " of " & lngCount & " records" ----------counter code---------
On the same form, I have a delete button. The button deletes the current record that is being viewed (user prompted before it is deleted). The code for that button is as follows: ----delete code----- Private Sub command271_Click() On Error GoTo Err_cmdDelete_Click
DoCmd.SetWarnings False If MsgBox("Are you sure you want to delete this Audit and its associated measures?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdDeleteRecord
CLIENT_NAME.SetFocus End If Exit_command271_Click: DoCmd.SetWarnings True Exit Sub Err_cmdDelete_Click: MsgBox Err.Description Resume Exit_command271_Click End Sub -------delete code--------
This button also works fine with the exception of the following. THE MAIN PROBLEM: example I have a client that has 5 different entries in my database (5 diff. facilities) and I want to delete one of their facilities. I would simply use my 'search' form and then navigate to the facility that I would want to delete. If that facility is facility 1, 2, 3, or 4 then I have no problems. The system deletes the selected record and then simply displays the one after. The PROBLEM is when you are trying to delete the 5th record. When the fifth (in this example, it could be the 9th record for any other client, as long as it is the last one) is the one to be deleted, an error occurs. When you hit the debug button the following line from the counter is highlighted: .MoveLast
I have solved this problem and I no longer get an error message BUT when the last record is deleted, the database simply loads a new and empty form that will accept data. I DO NOT WANT THIS TO HAPPEN. I want it to be set up as follows: If the last record (not the ONLY record, but the last record on file) is the one that is to be deleted THEN delete that record and load the one prior If there is ONLY ONE record, then delete it and load the main form.
I dont know if this is possible in MS Access (2003) but this is what I want to do...
I want to create a database, each record has about 8 fields. Usual basic stuff for a typical DB so far... BUT! One of the fields is the category info, say "Item 1" Usage: Now another 5 records are made and are childen of "Item 1" and called..say "1a" (..b,c,d,e) Item 1d record has 300 sub nodes to it.. say "Item 1d1" (2 to 300). And so on to eventualy make many records but linked by a huge tree like structure (like Treepad DB). Finding Records. Now, to find say "Item 5c22f5" I just want to see 1 drop down list, I then choose "Item 5" from it. Then a second drop down list appears with..say 8 child nodes of Item 5, I choose "Item 5c", 3rd drop down appears, I choose "Item...22" etc, etc. till I get to my "Item 5c22f5" Now, while sellecting each node, some nodes in other brances contained hundreds of items but I never want to see them unless I go through their brances. Adding new records: eg: While working on "Item 5c22f5" I decide to add a record which will be "Item 5c22f6", I want to simply add it within the same branch but not be expose to the hundreds of other nodes/records. Then I also would like to now add a sub of the current and call it "Item 5c22f6a". and also be be confided to the current branch. Or if I just go in 3 brances deep & see 5 levels each etc. Finally, each record will have a field called "Notes" but I want rich text formating (like in word pad would be fine).
Maybe the ID tagging fields could be 3x on each record to show forward, current & back nodes??? What ever it takes.
I hope I've explain this OK. I am new to MS Access so if it cant do it, I dont want to spend time learning it for nothing (at least not now).
This (if sugcessful) will be my universal, mind mapping info base. A baby I've been dreaming of for years now. Like Infohander meets TreePad meets Advance Data Managment (ADM) meets FileMaker.
Not availble anywhere in anyform as I know. Thanks in advance. (sorry about the spelling)
Hi... I searched quick, didn't find anythign, but you guys are so responsive... :D
Question: Part 1. On TimeSheet form, I want to click to "create new job", and the Jobs Form opens, at a new record. Currently it opens at record 1.
Part 2. I foresee that when I return from the modal job form, the old form (timesheet) would not have updated the combobox to show the jobname I just entered.
I guess this is what subforms are, because I keep reading about them, but dunno what they are.
Hey all, I was wondering if anyone might know how to obtain a reocrd count, I need to create a PERMIT number based on the number of records in my table. So I need the number of records in my table plus 1, which will then give me half of my permit number, I will then add the 2 last digits of the current year and add a "-" I think I have the date done, but I dont know how to obtain the number of records in my table? Can anyone help? Thanks
I'm building a health data system that stores data from health tests as follows: [test01], [test02], [test03],.........[test25], etc etc. Each of these tests comprised of a "Pass"/"Fail" value.
Now, i'm building my queries for a feedback report to hand over to the client that is based solely on the 1 single client record. In other words the queries are designed to return only 1 single data record to be placed in the client report where the based on the corresponding [TestID].
So within this 1 data record i want a query to count the number of times "Pass" appears in the test fields?
As there are some 40-50 [test##] fields in this one test battery i don'y particularly want to go back and code an IIf statement for each component if i can help it.
I have a query that updates 1 field in tableA which is the sum of values from 3 other related tables. However, when 1 of the 3 tables has a record count of 0 then the query does not work??? How can I update tableA excluding tables with record count <1 ?
I am working through a lot of data and this is basically what I'm trying to do.
Let's say we have billed occurances for ABC Pet Store. What I need to do, is to see how many "billed lines" appear in the data. So if there are 3 billing occurances (let's say bill amount greater than zero) - in the data I want the query to populate a "billing occurances" field with "3."
Cant find what I am looking for on the forum so if anyone can help me or tell me what to search for that would be great...
I need one of my forms to display a running total of updated records, but only those records that the user clicks a command button on. i.e if i have a recordset of n records each time I move to the next one (by clicking a command button) I want the control on the form to display the count of those records that have been updated only.
I am assuming I would need to add some code to the after update event of the form or on click event of the command button but cant really figure out what code to write.
In have a table which contains customer address details. I simply want to display a 2-column list. the first column to display a disnct list of city's from the main table. the corresponding column must give the no. of records that contain the entry in the first column.
eg if the main table had 5 customers that lived in london and 6 in Maidstone then
I have two tables - one contains customer names, the other customer appointments. So one customer - many appointments.
Each appointment is booked at a set interval (every 3 weeks, 4 weeks, 5 weeks...) which can vary from one appointment to another.
I want to do a count, in a query, to show in a report.
I need to count:
Total Cus_ID by interval - so how many customers are booked every 2 weeks, every 3 weeks, etc.
I need the count to be based on the customer's LAST appointment only.
I have tried, select query (group), crosstab (!)... querying a query... Total line using Max... then Last...
Nothing I tried works. The sum of appointments by interval should equal the total number of clients in the database... It gives me 4 times that... so it is counting every appointment, not just the last appointment entered.
I also will be including two other fields: activecustomer = yes and source = Eve - need to know criteria to set.
Does anyone have a good technique for enforcing a minimum number of records in a table?
I am attempting to ensure that in a table of Roles, there be a minimum of two Roles defined as Technicians. The field which identifies a Role as Technician is a Boolean.
I wrote a Function which does a domain lookup to count the number of Roles defined as Technician and am calling it from the BeforeUpdate event.
However, if there are 2 Technician Roles, and I mark a third Role as Technician, change my mind and try to unmark it...of course the Function can still see just the 2 existing.
I realize I can Undo the edit, but a user may not.
I’ve attached a zipped example of the problem I am having with customised navigation buttons.
In my main database the form “subfrmPersonsContact” works perfectly. I have exported it to a new database and now the customised record count fields do not work at all – they are in fact blank even though the navigation buttons do still work.
The code running the form and the record count fields is listed below – I have no idea why it’s not working.
Any help would be greatly appreciated.
Cheers,
Rusty :D
Private Sub Form_Current() On Error GoTo err_Form_Current
Dim rs As Recordset Dim Count As Integer, Position As Integer
Set rs = Me.RecordsetClone rs.MoveLast rs.MoveFirst
Position = Me.CurrentRecord Me!txtRecPos = Position
If Position = 1 Then Me!gotoPrevious.Enabled = False Me!gotoFirst.Enabled = False Else Me!gotoPrevious.Enabled = True Me!gotoFirst.Enabled = True End If
If Position = Count Then Me!gotoNext.Enabled = False Me!gotoLast.Enabled = False Me!txtRecCnt = "of " & Position Else Me!gotoNext.Enabled = True Me!gotoLast.Enabled = True Me!txtRecCnt = "of " & Count End If
rs.Close
exit_Form_Current: Exit Sub
err_Form_Current: If Err.Number = 3021 Then Resume Next Else Resume exit_Form_Current End If End Sub
I've searched the database and found similar problems but don't understand the replies (mainly DCount()).
I have a control form where a user can search the database by entering information. The form asks them to type some text that will appear in a description field, and match any records with the same text.
However, when the user types the text and clicks the button to open the form (which is linked to a query searching the description field), I want a message box to say no records found before opening the form. Mainly because when it doesn't find a matching record, the form opens with a new autonumber.
I either want a matching record to appear, or a dialog box saying no matching records found and the form not opening therefore not creating a number that is not used.
I tried in the command button entering if recordcount>0 then openform but it doesn't like the else statement or doing a if recordcount=0 underneath.
I've only recently gotten to know about continuous forms and their use a bit. So for example, I used a maximum number of pre-defined (6) visits. I need to change that still, but during the development of the sales forms I already ran into a problem.
At first I was planning to have a certain maximum number of items to have on a form and create an invoice from that. But of course that would limit the number of items on one invoice which isn't good. So, now I have a continous form which adds a line containing productname, price, nr of that item etc. anytime needed.
In the previous design idea it would have been easy to calculate the total amount of money at the end of the list since the list was predefined. So I could just say invoiceamount = itemID1amount + itemID2amount + itemID3amout etc. With the "new" design however that is impossible to do since the number of objects is unknown. So how do I create a piece of code which does that? In the continous form I have a SaleID which keeps track of which items belong to a certain Sale and a TotalItemsID autonumber which keeps track of the number of lines (items) which belong to that specific Sale.
So I need to count the total number of TotalItemsID's within a certain SaleID and then have a piece of code which creates a sum of that counted list of items.
I'm new at this, I've found the function Dcount() should be used to accomplish this, but the rest, as of yet, remains a mistery to me.
I have a Report Generated on Access Database, the report is 56 page now i'm tring to put the total records on that report, how do i do that ??? pls help
2) If I move rst to last position, number of recordcount is correct, but! If there's no record in my string I get an error that rst can't move to last position.