Modules & VBA :: Pull X Number Of Records From A Database
Jan 23, 2015
I need to first generate a random number between 1-4.
Then I would like to use that number to pull that many records out of the database; the records pulled can be random, or whatever.
For example, we have 4 people and my goal is to choose a random # of records to pull for each person. So, for person #1, it might only pull 1 record, then for person #3, it may pull 2 records, etc.
Ideally, it wouldn't pull the same # twice when run for that "session".
After this, I'm hoping to generate all those results into emails (eg for person 3 it would generate 3 emails, etc)
Currently it's all done manually and I think it's doable with Access.
I have some code already for generating "1" email message, but do not know how to pull X number of emails all at once. (X is the random #).
View Replies
ADVERTISEMENT
Jul 3, 2014
I have a module that is in Excel that is connecting to a back end database. I am trying to pull data from a table that meets a number of conditions. With the following statement, it is not returning any records,even though I know there are records that meet all of the conditions. I suspect the problem is with the last condition. In that condition I am trying to say that pull in records where it has been at least 14 days since the last review.
Code:
strSQL = "SELECT tblsojrol_oc.* FROM tblsojrol_oc WHERE tblsojrol_oc.[Status] = 'Pending' AND tblsojrol_oc.[1st Review Date] IS NOT NULL AND " _
& "tblsojrol_oc.[3rd Review Date] IS NULL AND (DateADD(Day,14,tblsojrol_oc.[2nd Review Date])) >= #" & dt & "#;"
objRs.Open strSQL, objConn, adLockReadOnly
View 14 Replies
View Related
Aug 27, 2003
After creating a recordset how do I display the number of records in it?
View 2 Replies
View Related
Oct 22, 2013
I have 3 tables invoice, customer and items. I need to create a query that will get the next invoice number from the invoice table and mark that invoice number in the items table for a set customer where the invoice field in the items table is blank.
The invoice table is now set up as a autonumber, therefore I need to append to get the last new number.
The items in the item table are unique and this is why I want to mark the invoice number in the item table field invoice.
View 1 Replies
View Related
Aug 28, 2013
I built a report that provides all of the open support tickets. My idea is to have a button (Which I called "WorkIssue_Button") that displays beside each record that is populated on the report... I need this button to be able to pull the ID number of the record (Called "ID"). I then plan on opening a form that is preloaded to this record. How to pull that information?
View 5 Replies
View Related
Nov 8, 2012
I have split the database, with the back-end residing on the server. Only 1 other person is working in Access right now; she's verifying the data. Today she's working directly in the back-end, could this be the problem. If it is, I'm going to have to create a front-end for her quickly.
My problem is this...I'm working on queries and forms so that the scientists who will ultimately be using this application, won't be able to go in and inadvertently change something in the tables set-up. When I'm in the back-end main data table, it says I have 2723 records.
When I create a front-end query to query all the records in that table, it says I have 2160 records.
The input form that I created with most of the same fields as the query (created BEFORE I created the query...I'll have it pull from the query now instead of directly from the table) also says we only have 2160 records.
Using Access 2010
View 5 Replies
View Related
May 27, 2015
I am looking into re-creating something I have created in Microsoft Excel into Microsoft Access.What I do in the Excel spreadsheet, is when I type in an order number, various fields in the spreadsheet are populated using vlookups and ODBC's.
I wish to re-create this in Access but not sure how. I have a basic form, with various fields such as, order number, part number, description, quantity.What I have in my mind is that I would like to be able to type in an order number, then the rest of the fields would be populated by a query to a SQL Server database.i can see a way to do this using VBA but would rather look for a more direct way if possible.
View 2 Replies
View Related
Jun 11, 2015
I have a table in a database with 7 columns. The data is sorted by Date1 in descending order.
For each pid I want to put the sequence numbers
First record has two conditions
If string 2 is null then start numbering from sequence1
If string 2 is not null then start numbering from sequence2
If string 2 = string1 then
Sequence1 = 0
Second record has two conditions
Number sequence2 with the value 2 or 3 depending on the line one
If string 2 = string1 then
Sequence1 = 0
Else
Sequence1 = next number
Same condition for the rest of records
View 5 Replies
View Related
Jul 23, 2014
I need to fill in 200000 records counting from 100000,100001,100002.... and so forth, just one column (and maybe the auto numbering).
make a new DB with these columns: ID, counter
set counter to 100000 where ID=1 (in the first record)
move to next record (or make a new record)
if ID < 300000 then set counter = 1+ (the value of counter in the previous record)
continue until ID=300000
View 8 Replies
View Related
May 3, 2014
I am having trouble with the below returning the correct number of records, and can't see why.
I have one table, tblDevice, which has 4 columns, ID | DeviceRecNo | ExcludeFromCheck | StockLocationID
ID - Autonumber
DeviceRecNo - Number
ExcludeFromCheck - Number (1 = yes, 0 - No)
StockLocationID - Number
I have the following running as part of some code, but it is not returning the correct number of records, and I cant see why not. I have tried creating this in a query in Access itself, and copy the SQL into VBA and it still doesn't return the correct number records.
I have 4 records in there for testing, all with StockLocationID = 3, all with different DeviceRecNo, and two each of ExcludeFromCheck, 2x0 and 2x1
Code:
Public Function test()
Dim db As dao.Database
Dim rs1 As dao.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT DeviceRecNo FROM tblDevice WHERE StockLocationID = 3 AND ExcludeFromCheck = 'No'")
Debug.Print rs1.RecordCount
End Function
View 3 Replies
View Related
Jul 11, 2014
I have a table which has 2 fields 1) Project_Priority_Number and 2) Previous_Priority_Number. If there were 100 records these would be numbered 1-100 in the order that the user originally sets the priority (this number is in addition to the record ID number). I have created a form with code that moves the Project_Priority_Number to the Previous_Priority_Number and then shows the Project_Priority_Number as blank and displays all of the records.
The user can then set new priority numbers in the blank column. Say they choose to make the old priority number 4 the new number 3 and priority 27 now becomes say 2, etc. I want the user to press a button that re-numbers the remaining ones based on their old position + or - 1 (basically to fill the gaps but based on their previous positions). I understand how to renumber if one is deleted but I don't know how to be more specific and re-number based both on their previous priority number and whether something else is now set to replace that.
For Example (the following numbers need to re-order based on previous priority 4 becomming the new priority 1 and the previous number 8 becomming 3 - so the previous 1 becomes 2 and then everything after the new 3 moves forward 1):
PROJECT PRIORITY NUMBER PREVIOUS PRIORITY NUMBER
1
2
3
1 4
5
6
7
3 8
9
10
View 14 Replies
View Related
Sep 10, 2013
I am trying to calculate the number of days between incidents.
If I have an incident that happened on the 10th August and another on the 15th August then this would be 5 days, the next incident occurred on the 28th August so that would be 13 days. The problem I am having is how to get access to use the latest date rather than the first date.
View 14 Replies
View Related
Nov 1, 2013
We have a database that is split. Every user has their own front end (installed via a .bat file so that the users always have a local copy of the most recent version), linked to a shared back end on a server. This seems stable and functional for us so far.
However, because our users are geographically separated, I don't actually know how many folks are using the database. I know the folks who contact me, but everyone in our company could use it if they wanted to.
For requesting funding, guiding future development, etc., I need to get some metrics:
Total number of unique users
Avg uses per day
Total number of simultaneous users (daily, weekly, and monthly maxes and mins)
What is the best way to get this info? Write user info and a timestamp to a table each time a front end is launched? Are there tools for monitoring this sort of information?
View 3 Replies
View Related
Jan 17, 2015
creating the VBA code to find the total number of tables in my database.
View 2 Replies
View Related
May 30, 2014
Lets say you have a table with an AutoNumber field.You populate it with 100 records.You delete the last 10 records (91-100)Now, I certainly know you can refresh the AutoNumber sequencing by compacting the database so the next record entered is 91.
My question is about NOT refreshing the AutoNumber sequencing.Lets say you DO NOT COMPACT and DO NOT refresh the AutoNumber Sequence.So, in that scenario, after deleting records 91-100 from the table, is there any programmatic way to determine that the next actual AutoNumber for that table will be 101?
View 3 Replies
View Related
Sep 24, 2014
I have a table which specifies the delivery date
I have a from that allows you to choose a year and a month.
I have an unbound textbox which I wan to display the count
I want to be able to count all the records from a table with the year and month specified in the comboboxes and display this in the texbox.
View 4 Replies
View Related
Jul 4, 2015
I have a form which uses a loop command to output reports as a PDF. The reports take a bit of time to produce and the record set could contain 100-150 records. Any way that you can split the recordset down into batches. Maybe have a button which creates 1-20 and another 21-40 and so on.
View 1 Replies
View Related
Aug 30, 2013
creating auto number field in access database. I have an access database which 20 million records. When i am trying to add auto number field i am getting error "File sharing lock count exceeded".Then i did some google search and got some information like editing the registry file , in my case its not possible due to security restrictions.And another option of adding a code in VB immediate window also i tried but this option is also not working.
"DAO.DBEngine.SetOption dbmaxlocksperfile,25000000"
how to auto populate the numbers in a specific field using VBA codes.
View 4 Replies
View Related
Nov 14, 2006
Hi,
I'm totally newbie so please go easy on me, I finally learnt how to make database and tables by reading online tutorials. I'm now able to create a simple database in access, using tables and stuff. I want to ask, say i put 500 entries in my database and then i want to search for some specific data, how do i do that?
Say I'm making a database of model's agencies, and i want to pull all the models working in same agency and stuff like that, how do i do that. Do i have to create queries or forms? i'm confused, please help.
Tania.
View 1 Replies
View Related
Jun 13, 2013
I have a code that works great from the parent form but I decided to change the format and call it from a lostfocus event in the subform instead. Now I keep getting error 3314:"You must enter a value in the tbGuests.LastName field".
The code should copy the parent form fields and create x number of duplicate records according to a field on sub-form. It then runs an append query to add the information from the subform.
Code:
Private Sub GuestsInParty_LostFocus()
Dim partymsg As Integer
Dim dbs As dao.Database, rst As dao.Recordset
Dim F As Form
Dim intHowMany As Integer
Dim intCounter As Integer
[code]....
View 1 Replies
View Related
Mar 17, 2007
Hiya all,
I know the title is confusing, and so is the problem (to me at least). Basically, I have a database with an invoice form. Each record is an invoice, and on this form there is a subform of additional charges. The tables are linked by InvoiceID. It worked smashingly when I was playing around with it, until I finally tried an invoice with no additional charges. The queries for my reports display no records (because there are none). This is an issue, because I need a summed up amount of the charges to complete the invoice. I cannot do this on the report, because additional expressions are used on the charges.
Basically, my problem comes down to this: I have no additional charges, so certain fields in my query don't exist, therefore the entire query record won't display. I was screwing around with NZ() and IsNull() before I realized that a null record and the absence of a record are different.
I've been searching, but can't seem to find the right keywords to produce a fruitful search. If anyone can point me in the right direction or give me some words of advice, I'd greatly appreciate it. And yes, as far as I know, my database is fully normalized.
View 11 Replies
View Related
May 29, 2014
I need to extract a specific number of records into a table using a MakeTable or Append command using a temp variable, e.g. TempK&SA. Previously on the forum I was shown how code could be added to the OnOpen function to use a temp variable to select a specific number of records to report. ACCESS does not have the OnOpen function in the design view of a query like in the report. It does allow a SELECT TOP but only with fixed variables or percents (e.g. 25 in the code below).
The beginning code for the make table query (where 25 is the number of records added) is:
INSERT INTO [Output] ( RndNo, PointBiserial, BloomsTax, DateRevised, Exam1, Status, Exam2, Exam3, Exam4, [NCCPAKnowledge&Skills] )
SELECT TOP 25 TestBank.RndNo, TestBank.PointBiserial, TestBank.BloomsTax, TestBank.DateRevised, TestBank.Exam1, TestBank.Status, TestBank.Exam2, TestBank.Exam3, TestBank.Exam4, TestBank.[NCCPAKnowledge&Skills], *
FROM TestBank
WHERE (((TestBank.PointBiserial) Is Null Or (TestBank.PointBiserial) Between [TempVars]![TempPointBiserialLow] And .....
how to modify the code to allow a temp variable to determine the number of records to append to another table would be gratefully received. (This process then is repeated for a total of 7 append tables with different temp variables.)
View 7 Replies
View Related
Jun 28, 2013
I want whenever I'm updating or adding records to my form, the ID automatically take the value of the previous ID and increment it by 1.
The field type is text (mixed with number) - PM0000000.
I've done some research, what I understand is that I need to:
-do a lookup and
-find the MAX of the number portion.
Name of form - Payment
Name of table - Payment
Name of field - payment_id
I tried these, but to no avail...
Private Sub payment_id_Click()
payment_id = DLookup(("[payment_id]", "Payment", "[payment_id]=Forms![Payment]![payment_id]-1")payment_id + 1)
End Sub
[Code] .....
View 8 Replies
View Related
Jan 19, 2015
Code:
' count records in query
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Dim beginDatum As String
Dim eindDatum As String
Set db = CurrentDb
[code]....
View 4 Replies
View Related
Sep 23, 2007
I have this basic question (obviously not so basic for me): how to pull out only unique records from two fields.
More details - two fields with names of competitors in a tournament (Winner or Loser) and i need to build a query to have all players names participating in the tournament regardless if they have won or lost in one list.
View 4 Replies
View Related
Mar 18, 2014
I have a table with the following values.
EMPLLOYEE_ID
JOB
PAY_DATE
LOCATION
Most employees have 10 to 15 rows. I only want to pull ALL rows for employees ONLY if there is a distinct count from DATE_PAID greater than 1. There are cases where an employee might have two pay checks generarted for the same PAY_DATE.
View 3 Replies
View Related
