Hi,
Can someone please help. I am required to bring back a ranadom 10% of data where the [Total_Cost] field is less than £500.
Can this be done in one query? I have to use Access97 becuase this is the lowest Office Version we support.
:confused: .
I want to use the same form in datasheet mode for data entry and retrieval. When retrieving, all controls are disabled and locked. I am trying to enable and unlock them for modifying but that isn't working.
<code>
DoCmd.OpenForm "PO_Practice Data", , , , acFormEdit, acHidden
Forms![PO_Practice Data]!PO_Name.Enabled = True
Forms![PO_Practice Data]!PO_Name.Locked = False
Forms![PO_Practice Data]!Practice_Name.Enabled = True
Forms![PO_Practice Data]!Practice_Name.Locked = False
[code]...
I have an entry Form which is used to record prospective new members of a society in a Table named 'Foreigners' in a Membership Database using Access 2010. The entry form also records the name of the 'Interest Group' to which the prospective member is initially affiliated.
On entering this Group Name, selected from a drop down list from the Groups Table, The Group ID & Leader Member ID are automatically recorded.
However, I don't wish to record the Leader Member ID, what I wish to record is the Leader's Member Name which is identified by that ID in the related 'Mail List' Table.
In the Properties sheet the relevant source for this field is shown as Leader, but what is displayed is the Leader ID...
Hi all
I would like to find out if there is a program where you can randomly select data out of a table?
I sit with a table with 17000 SKU's(codes and data) and need to randomly select items out of it for Stock Count's:confused:
Thanx:rolleyes:
I have almost all of my info with the exception of few hundred pieces of data entered into my database. After looking it over and looking for ways to improve it, would it be easier to import all the tables into a brand new database and starting fresh with everything? I have made a lot of changes since the inception of the database so there are forms and queries and reports that no longer work because of the changes. Or should I go back and change them?
Also, if I am looking to join to tables can they be joined by their foreign keys? They are listed below:
tblEmpInfo
EmpInfoID <PK>
EmpIDFK <FK>
JobNumberFK (joined to tblJobs)
EmpType
CraftCode (would get rid of this)
tblEmpRating
EmpRatingID <PK>
EmpID <FK>
EvalDate
SuptID
StatusChange
CraftCode
I want to join the tables based on the EmpID can this be done or do I have to add another field to EmpRating ID?
I've got an Excel sheet with +700k rows and 20 columns that I wanted to import to Access. All fields are text except the field that I want to use as a primary key, but I planned to import that as a text as well.
When I used the import wizard, I set all fields to import as text except for three that I set to memo. The wizard didn't say there was any error after importing the data, but when I checked the table, I noticed there were *a lot* of records where many fields where blank. Some fields where completely unaffected by this problem throughout the entire table, but in the rest of them, there is data missing in many records, and when there is data missing, it is not always the same fields that are missing. I have been unable to find any pattern that explains why sometimes the records were imported correctly, and why sometimes they were not.
I am not sure whether this is a problem with MS Access, Visual Basic or Windows.
I have taken over supporting & developing an MS Access 2000 DB for a small charity & am not an Access expert or a programmer. The Application includes processing to create, amend & store retrieve standard letters based on a Word document called MyMerge.doc. The operating systems is Windows XP for the PCs with a MS Server 2003.
Each letter is allocated a number ‘CallID’ which is used to retrieve the letters later. The letter text is in MessageC.
The VB code to store the letters (Save As) is
Dim strTest As String, db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb
For Each td In db.TableDefs
If Len(td.Connect) > 0 Then MessageE = Mid(Left(td.Connect, InStrRev(td.Connect, "") - 1), 11)
GoTo jumpout
Next
jumpout:
MessageC = "%fa" & MessageE & "Db Letters" & MessageC & " " & (CStr(Forms!Contacts![PostalCode])) & " " & Trim(DLookup("[TitleType]", "Title Types", "[TitleTypeID] = Forms!Contacts![TitleTypeID]") & " " & Forms!Contacts![FirstName] & " " & Forms!Contacts![LastName])
objWord.Application.Activate
SendKeys MessageC
The VB code to retrieve the letters is
Dim MessageE As String, db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb
For Each td In db.TableDefs
If Len(td.Connect) > 0 Then MessageE = Mid(Left(td.Connect, InStrRev(td.Connect, "") - 1), 11)
GoTo jumpout
Next
jumpout:
Dim WordApp As Word.Application
Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True
WordApp.Application.Activate
MessageE = "%fo" & MessageE & "Db Letters*" & CallID & "*.doc"
SendKeys MessageE
Set WordApp = Nothing
GoTo Exit_Command53_Click
The success rate varies from PC to PC and user to user. It will work with one letter and not the next. It is very difficult to identify a pattern. When the Save As does not offer the expected name & path, the Application (or operating system?) offers to save ‘MyMerge’ to the user’s My Documents folder.
In that case I tell the users to correct the path themselves and save the document under the CallID. Theoretically, retrieval should work since this uses the CallID and wild cards. Sometimes it does but often it will instead retrieve a document in the user’s My Documents folder. If it does go to the right folder, you sometimes have to replace the last wild card with ‘.doc’. I have tried replacing the last wild card in the VB code with ‘.doc’ but this does not work!
Finally, I alone get the message ‘Save failed due to out of memory or disk space’, neither of which is true. This makes it very difficult to continue investigating the problem. If I use a copy of the DB on my hard drive rather than the network I can save but not retrieve.
I am trying to build a database for my digital photo collection.
(having pretty much gven up on the packages I see in the market)
Since most of the images reside on CD's and DVD's, I want to create
and store thumbnails and pointers (ie. location fully qualified name),
rather than a copy of the full image.
1. How do I define the fields in the table for the thumbnail, and the pointer?
2. How can I use the pointer value to retrieve the full image?
Any ideas and tips on where to start?
Thanks,
Roy
Hi,
I want to retrieve data from 2 columns using only 1 search option. At the moment I have 2 ID fields which contain various numbers. My data retrieval functionality only retrieves data on either one column or the other using 2 search buttons. So for example, if they want to retrieve data on ID column1 they have to click the button named this and if they want to retrieve data on ID column2 they have another button for this. What I want to do is when the user clicks the button I want access to prompt them for an ID number (only want 1 prompt). Once the ID number is input I want access to search both columns for the ID number and return it. Any ideas on how this can be achieved plz?
Thanks
I'm very confused. I have two tables in my db that should be the same except for columns(I copied one table from the other). In each I have a "default" entry as the first record in the table just to prevent my select queries from erroring out if they don't find a match for the criteria. When I use the following statement,
Code:SQL="SELECT ID, Name, Picture FROM background WHERE Name ='" & Fname & "' OR Name='Default'"
it returns the default value as the record, not the match for "Name", even though there is one(when I remove the OR Name='Default' it pulls the record).
When I use this query for the other table from the same .asp page,
Code:SQL="SELECT ID, Date_Time, URL, Icon, Title, Description, Status FROM Submission WHERE Technician ='" & Fname & "' OR Technician='Default'"
it returns the record where "Technician" matches even though it is below "default" in the table.
I want the query to return the "default" record only if there is NO match for the first criteria, which it does in the second case, but not the first. Is there some way to force this? Thanks in advance for any assistance provided! -Chris Gordon
I am trying to run a Query that searches for a single Tool ID Number and returns the transaction record that has the latest date. My query currently is this:
SELECT LocationStatus.ToolID, LocationStatus.CurrentLocation, LocationStatus.DateofEvent
FROM LocationStatus
WHERE (((LocationStatus.DateofEvent)=(SELECT MAX(dateofevent) FROM [LocationStatus] AS t2 WHERE t2.[ToolID] = [Tool Number])));
But the problem I am having is that the query is returning MULTIPLE Tool ID numbers with matching dates. Before the query runs a parameter box pops up asking for a Tool ID number but there should only be one record returning. HELP!!!:confused::confused:
Why I getting an error when trying to run the below code ? If I take out
Me.ClientNameList.Column(1) = rs.Fields("[Tracking Date]") Then . . .
add "And" to
If Me.ClientNameList = rs.Fields("[Client Name]") . . .
I do get a record, but with the wrong date. I need to match the client's name and tracking date, then move the related fields to a MS Access form.
The code follows:
Private Sub ClientNameList_Click()
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Progress Tracking")
[Code] .....
Our organization (a University) has been engaged in becoming "paperless."
As part of that initiative, we have spent the past several months scanning several hundred thousand pages of documents relating to building systems (HVAC, e.g.) as well a a massive quantity of detailed lead and asbestos surveys and abatement records.
The initiative is indeed reducing the quanity of paper stored in binders and file cabinets, however now it is becomming evident that retrieving some of that information is a real problem for some folks.
At a meeting this morning a question arose about whether we could develop an Access application to retrieve the documents (which are very logically filed on a network share, but it seems to be beyond the compreshension of some staff how to actually navigate through the maze).
My question is this: has anyone in this forum ever tried to implement such a solution with Access?
Any advice or insight would be very much appreciated.
Thanks in advance.
Hi,
I build the WHERE clause in my form's record source dynamically, depending on the context in which the user opens the form. I can't put the WHERE predicates in the record source and refer to fields on another form, as the context will determine which predicates are required. So I have code like this:
Public gf_FormInstance As Form
Dim strSelect as String
:
Set gf_FormInstance = New Form_F_PerformanceSummary
:
gf_FormInstance.RecordSource = strSelect
gf_FormInstance.Refresh
gf_FormInstance.Visible = True
The problem is, the Set statement causes the New form instance to retrieve all records from the existing record source, slowing down the performance.
I'd like to find a trick to suppress the retrieval of records when the Set statement executes, and then allow them to retrieve when the Refresh statement executes.
Any suggestions would be much appreciated.
Thanks,
Keith.
I have a small unbound form which has a txt box with code something like =Rnd((20-1+1)*Rnd()+1)) to generate random numbers. When the db and form are first opened the field always contains the same number. Using F9 to refresh will generate another number but it is always the same too.
If the form is closed then reopened (db is not closed) the field will happily generate a random number each time the form is opened or if F9 is used.
I have tried Recalc in numerous events but each time it failed to work. It's late at night here so I'm hoping the morning will bring a clearer outlook???
Has anyone got any suggestions? :confused:
Was playing around, and when using the
Code:Me.txtNum1.Value = Int((51 - 1 + 1) * Rnd + 1)
format, I noticed that, with 6 differnet text boxes, that after opening the database, the "random" numbers repeat in the same order. That is, with 6 randomly generated numbers, the first set is always the same and the second the same and so on.
Why is it not really random?
Hi,
Can you please help me out--give me some guidance-if you have knowledge about random numbers. Say, for instance, I have 2,000 records and I want to randomly create a final list with 800 of these records, how do I go about it? Thanks.
Hi, I have a DB that is basically a big electronic form. Each table is a section of the form and the main central table stores the ID's from the other tables. Most of the relationships are one to one but without referential integrity because sometimes sections need to be skipped.
It's been growing fairly rapidly lately as bit's keep getting added but now a weird deletion problem has started appearing. Every once in a while when a user goes to complete the form (right at the end) it suddenly dissapears. The records in the related "section" tables are still there but the main table storing all the ID's loses the record.
There are NO cascade update/delete's on any of the relationships, also no-error message appears it just goes. I think there is a problem in actually saving the record because I moved a DoCmd.RunCommand acSaveRec command from the complete button to a slightly earlier process and the point at which the record dissapeared also moved.
Does anyone know what may be causing it? It only happens to about 1 in 30 forms and there is no obvious link between the problem and
a) the machine it happens on
b) the person using it
c) the data entered into the form
If it happens the same user can input exactly the same data in exactly the same way and it saves it fine.
It really is random!
People are starting to get really frustrated. I was tring to "sell" the idea to a new department as well and had 15 people sitting in front of a projector when it happened to me. I was mortified :eek:
Any ideas would be greatly appreciated.
thanks,
Tom
Hi,
everything was going fine until a random error appeared and now wont go away.
here is my code
MainFormPay()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = " Payment Numbers" ' name of form t open
ANumber = [LastOfVerificationID].Value ' set value of latest Verify number
stLinkCriteria = "[ContractorID]=" & me.ContractorID ' filter to contractorID
DoCmd.OpenForm stDocName, , , stLinkCriteria
Docmd.GoToRecord, , acNewRec
Forms![Payment Numbers]![VerificationID].Value = ANumber
end sub
this was working fine but now i get the following error message:
Run-time error '40036'
Method 'Item' of object 'Forms' failed
when i debug, it highlights the line
Forms![Payment Numbers]![VerificationID].Value = ANumber
the purpose is to get the LastOfVerificationID value from form 1 and then open Payment Numbers, create a new payment number, set verificationID to the new value. this was working fine and without even woring on the forms code, this error has arisen.
how can i fix it?
Nigel
With Access 2003 I have a tbl_Orders with the field OrderID set as Autonumber>Long Integer>Random. This produces long strings of numbers both positive and minus. Is it possible to set a certain format such as #### and no less than 0 but no more than 9,999?
View 3 Replies View Relatedhi everyone,
i have a query which returns n number of rows. my problem is that i need to display the values in text box which is created automatically according to the number of rows returned in the same page(form).
for example if my query returns 1 row then 1 text box must be automatically created and then the value must be displayed and if my query returns 3 rows then 3 text boxes must be created and then the corresponding three values must be displayed.(on the same page).
is this possible?
aravind.s
I'm looking for a way to generate a random list of 25 case numbers from a table which contains 500case numbers. Can someone tell me how to do this.
View 1 Replies View RelatedQuick question, how do I have access generate a random number between 1 and 8? I want it to generate either a 1,2,3,4,5,6,7, or 8. (no zero, no nine)
Expr1: Fix(Rnd(8-1)*10*Rnd()) is what I have been using, but this always generates the number 2? I do want the decimals to be rounded to the nearest integer, thus I have Fix.
If someone could please help me, it would be greatly appreciated! Thanks in advance.
Hello,
I have a very complex issue.
First of all there is a table called "tblParTeam" wich contain the teamname and ID of teams partissepating in a certain Tournament. The tournemant data is kept in the table tblTournament.
Each tournament contains a couple of variables: "Number of teams per tournament", "Number of Rounds" wich reside in the tblTournament.
Here is the thing. I also have a form based on the tblTournament. Each tournament had a number of partisipating teams (stored in the variable "Number of teams per tournament"). Then I have a table called tblTournamentDetail wich containt the Tournament_ID, TeamPro_ID (looks in the table "tblParTeam"), TeamContra (also looks in "tblParTeam").
When I pusch a button on my form, wich is based on the tblTournament, tblParTeams and tblTournamentDetail, I would like the X (Number) of particepating teams to be filled in randomly in the tblTournamentDetail.
Lets say I have 10 teams I want 5 to be filled in randomly in the TeamPro_ID and 5 in the TeamContra_ID.
How do I go about this?
Thanks in advance.
I need to select 200 random customers from my table, how can I do that?
Table: tblCustomers
PK: CustID
How can I make random auto numbers 8 digits in length (including leading zeros) and always positive?
Thank you