Ms Access 2003. Is there any way to find out what is using a certain query?
I need to know if qryPFP is used by other reports or forms before changing it. I don't want to mess up anything else somewhere else. Does this make sense? We have a LOT of forms, queries and reports and it's hard to plow through everything.
This may not even be possible, but I am looking to create a Query that can locate records in an Access Table based on 2 columns of data that I have stored in a CSV file.
My table contains several fields, 2 of which are "Dept" and "SKU" and has over a million records.
My CSV file contains 3 fields: "Dept", "SKU" and "Total" - total being the number of times that particular Dept/SKU combination is used.
I need to be able to parse the dept/sku values from each row in the CSV to the query and locate only the records that contain the same values in the Access table.
The plan being to delete out those that are identified by the query.
I have a combo box that displays LastName FirstName.
If I have two people with the same lastname it finds the first one in the table w/ that last name. Please help with my code so it searches by firstname and last name. Thanks in advance.
Kacy
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Contact_LastName] = '" & Me![cboContact] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
Hi
I have a search form with 1 combo box and a button which when the user inputs an invoice number and clicks the button, a main form is opened for that particular record.
The problem I have is to display an error message before the main form opens if the user has either left the combo box blank or they have input an invoice number that doesn't exist.
This feels like it should be easy......!
Thanks for any help
Georgina
I got this function which is supposed to find a record based on the primary key, and then give three fields a value if they should be empty.
Public Function DoesXrefExist(faPersonID As Long, faRoleCode As String, faDorpID As Long, faDorpCode As String)
If (DLookup("PersonID", "PEOPLE", "PersonID =" & faPersonID) > 0) Then
End Function
Thats as far as i have got...for the next part.I get the feeling that i need to use the recordset object that exists somewhere.How do i access that recordset object? I have not been using any DAO or ADO, just straight VBA. I know i can create recordsets and stuff using DAO/ADO, but there must be a recordset that has been created already by the Dlookup command...
I currently have a form to manage inventory. On the left side of the form, I have a list box that shows all of the items in the database and finds the record on the form based on the selection.
I would like to have toggle boxes or something underneath the list box that when clicked will sort the box by vendor and item and another that when clicked will sort the list by part number and then select the record on the form based on the selection at that time.
I have no idea how to accomplish this...can anyone help?
I have a form with a dropdown box that shows the 'Group by' records in a table.
For example the form shows "Art_6". My table has many records (could be 1000's) that start "Art_6".
I want a macro/query to find all records that start "Art_6" and delete them.
How to find only those that start as described.
I have a price table:
Code:
tblPrice
PosNr PriceDate Company Price
1 01.01.2014 Firma A 5
2 02.01.2014 Firma A 7
3 03.01.2014 Firma A 9
4 04.01.2014 Firma A 8
5 06.01.2014 Firma A 6
6 02.01.2014 Firma XY 11
7 03.01.2014 Firma XY 9
8 04.01.2014 Firma XY 7
9 05.01.2014 Firma XY 8
10 06.01.2014 Firma XY 10
And I have a table with the dates, for which I need a price.
Code:
tblDates
PosNr PriceDate Company
1 01.01.2014 Firma A
2 02.01.2014 Firma A
3 03.01.2014 Firma A
4 04.01.2014 Firma A
5 05.01.2014 Firma A (no price available)
6 06.01.2014 Firma A
7 02.01.2014 Firma XY
8 03.01.2014 Firma XY
9 04.01.2014 Firma XY
10 05.01.2014 Firma XY
11 06.01.2014 Firma XY
And now I want to combine this tables, and for the dates which have no price, the last price should be taken.
Code:
tblResult
PosNr PriceDate Company Price
1 01.01.2014 Firma A 5
2 02.01.2014 Firma A 7
3 03.01.2014 Firma A 9
4 04.01.2014 Firma A 8
5 05.01.2014 Firma A 8 (actualy no priceavailable, so take last price)
6 06.01.2014 Firma A 6
7 02.01.2014 Firma XY 11
8 03.01.2014 Firma XY 9
9 04.01.2014 Firma XY 7
10 05.01.2014 Firma XY 8
11 06.01.2014 Firma XY 10
how I can get this?
I have this code, but it need hours.
Code:
SELECT tblDates.PosNr, tblDates.Company, tblDates.PriceDate, (SELECT TOP 1
B.Price
FROM
tblPrices As B
WHERE
B.Company = tblDates.Company
AND
B.PriceDate <= tblDates.PriceDate
ORDER BY
B.PriceDates DESC ) AS Price
FROM tblPrices RIGHT JOIN tblDates ON (tblPrices.PriceDate = tblDates.PriceDates) AND (tblPrices.Company = tblDates.Company);
I am trying to add a combo box into a form and when the Combo Box wizard pops up I only get two choices:
I want combo box to get values from another table or query. I will type in the values I want
I want the third option...
Find a record on my form based on the value I selected in my combo box.
Why am I not getting the third option??
Can someone tell me how I migh find a record in a main form based a a selection in my subform?
I have a Main form called frm_ProductionSchedule.
It contains three subforms:
frm_ProductionSchedule_subform
frm_ProductionSchedule2_subform
frm_ProductionSchedule3_subform
I would like to be able to select a record within any of the subforms and have the main form display it. The main form is linked to a SQL server Table. The subform are based on queries. The unique PK field is a date field called record_date.
I should add that the Link Child/Master fields are blank for all Subforms. Each Subform is linked to a query and displays data for a particular machine (1,2 or 3) AND is updated based on a combo selection of the Production week. The queries for one subform would look something like this:
SELECT dbo_tbl_ProdSchedule.PartType, dbo_tbl_ProdSchedule.PartNumber, dbo_tbl_ProdSchedule.Work_order_no, dbo_tbl_ProdSchedule.FrameType, dbo_tbl_ProdSchedule.FrameNumber, dbo_tbl_ProdSchedule.Shift, dbo_tbl_ProdSchedule.Sch1_date, dbo_tbl_ProdSchedule.Sch1_spins, dbo_tbl_ProdSchedule.Sch1_sheets, dbo_tbl_ProdSchedule.Sch2_spins, dbo_tbl_ProdSchedule.Sch2_sheets, dbo_tbl_ProdSchedule.Sch3_spins, dbo_tbl_ProdSchedule.Sch3_sheets, dbo_tbl_ProdSchedule.Sch4_spins, dbo_tbl_ProdSchedule.Sch4_sheets, dbo_tbl_ProdSchedule.Sch5_spins, dbo_tbl_ProdSchedule.Sch5_sheets, dbo_tbl_ProdSchedule.Sch6_spins, dbo_tbl_ProdSchedule.Sch6_sheets, dbo_tbl_ProdSchedule.Sch7_spins, dbo_tbl_ProdSchedule.Sch7_sheets, dbo_tbl_ProdSchedule.Frames_due_date, dbo_tbl_ProdSchedule.Comments, dbo_tbl_ProdSchedule.Record_date
FROM dbo_tbl_ProdSchedule
WHERE (((dbo_tbl_ProdSchedule.Sch1_date)=[Forms]![frm_ProductionSchedule]![cmbSchDisDate]) AND ((dbo_tbl_ProdSchedule.SpinCoaterNo)=1));
AND WHERE ((dbo_tbl_ProdSchedule.SpinCoaterNo)=2)), etc. Respectively for each subform.
Thanks in Advance!!
John
I'm trying to find a subform record from a separate pop-up form based on its ContractID. When I click the button, I get the following error:
"A macro set to one of the current field's properties failed because of an error in a FindRecord action argument."
When I step through the code, it works just fine. Here is my code:
Code:
'Find contract
With Forms!frmContractForm.frmContractSub
.Form.ContractID.Enabled = True
.SetFocus
.Form.ContractID.SetFocus
DoCmd.FindRecord Me.ContractID
.Form.Business.SetFocus
.Form.ContractID.Enabled = False
End With
I am 2 years into my database. I am trying to find and open a folder based on a text box. The problem is folder could be in many sub folders which is hold on our J: drive.
To further complicate, the folder i am searching may not be exactly as the text box states.
EG. Text box could say 123456 however the folder could be called M123456 etc.
How do i locate a folder or subfolder and open it based on part of a text box...
I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?
View 14 Replies View RelatedI have a report that gets its data from a query. I need the query to run before the report based on criteria based from two combo boxes on a form.
View 3 Replies View RelatedHello All,
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)
Hi all,
stuck on this, not sure if it can be resolved....
I have a parameter query for which the parameter is based on a form field entry (i wanted to avoid the dialog box popping up).
No problems with this....however, I want to create a chart based on the same query however, i get the following error message because it does not recognise the query parameter.
Error Message: The Microsoft Jet database engine does not recognise '[Forms]![frmChooseDTDate]!DateOccured' as a valid field name or expression.
Can this be resolved?
Help most appreciated!
regards to all
K
The strSql string should find the record that I have already saved in my admin table and then compare it with combusername, where is my mistake.
Private Sub Command7_Click()
'Dim db As Database
Dim strSql As String
'Set db = CurrentDb()
strSql = "Select adminpass From admin"
If ((Nz(txtpassword, "") = "") Or (Nz(combusername, "") = "")) Then
MsgBox "Please Enter A Valid Access", vbOKOnly, "Error"
ElseIf (txtpassword = "admin" And combusername = strSql) Then
DoCmd.Close acForm, "Secure"
DoCmd.OpenForm "Home"
ElseIf (txtpassword = "user" And combusername = "user") Then
DoCmd.Close acForm, "Secure"
DoCmd.OpenForm "Home"
Else
MsgBox "Wrong Password, Please Try Again", vbOKOnly, "Error"
combusername = ""
txtpassword = ""
combusername.SetFocus
End If
End Sub
hi all, im trying to make a query so that when someone updates a field in a form it searches a table (Companytable) which is a list of company names. If it exists i want it to then display a msgbox " already exists" sort of thing! trying to do this in the criteria field on the query. anyone have any ideas or useful resources, ive not done this for a while and cant remember much of IF THEN sort of stuff. mainly not sure if im barking up the right tree, i can search dependent on a set value but not sure how to do it dependant on whats been typed into a form..any pointers would be appreciated! thanks! James
View 2 Replies View RelatedHi,
Our Contacts database has around 4000 individual contacts (and growing), which have been pulled together from four or five seperately maintained databases. As such there are bound to be duplicate records.
What would be a good way of querying the database to find duplicate entries (i.e. entries where the first name and surname are the same)? Short of putting everything in alphabetical order and manually scanning them to see if there are two names the same next to each other I don't know of a way to do it. Also, the manually-scanning method won't work if the names are spelled a little differently (Davies and Davis).
Any ideas?
I have a DB that has lots of queries, I'm trying to find the percentage of trainees that are employed from the number of trainees in my database. For testing purposes, I have three trainees from NCN and two of those trainees are employed.
Now, i made a query to find the total number of NCN-trainees employed which has three tables and seven fields (Fname, Lname, Group[NCN],Employee Status[full-time, part-time,un-employed],employer number, and employer). three records in total
The other query I made is to find the total number NCN-Trainees. This query is based on the same three tables but only has 4 fields (Fname, Lname, group[NCN], and employee status) two records in total
The trouble I'm have is which field to put the totals under.
Any suggestions??
Hello all,
could annyone help me with this?
i have a database with two tables in it, table x and table y,, i would like to find those records in table x that are not in table y, both tables contain the same ACTID key,, i have used the find un-matched query,, i'm just looking for a finid matched query if one exists???
many thanks
;)
I know I have duplicate records in a table and therefore I used the "Find duplicates Query Wizard" to identify the duplicates.
I am positive - there are duplicates and the query does not perform the way it should be.
Any suggestion will be appreciated.
TKS
JLA
I'm trying to query to find the latest date of a file that has multiple dates. I need the criteria to be the last date, and not just display the last date so I can find the transaction. I thought Dmax may work, but I don't know what my criteria would be. I put an example below. I'm looking for the last date, which would be 5/5/2014. I can't put in the date as the criteria since I am using this for a large amount of files, and the dates differ.
File #
setupdate
transaction
100001
1/1/2014
1
100001
3/5/2014
2
100001
5/5/2014
3
How do a write a query to find those rows in a field that have only 5 characters?
View 2 Replies View RelatedCan anyone help me in creating a field from a query which will pull the first word only from a specific field.
Thanks.
Hi.
i.e..............
Table A has records 1,2,3,4,5,6,7,8,9,10
Table B has records 1,2,3,4,5,6
How do I create a query that returns values 7,8,9,10 for Table B when I compare Table A and Table B?
What sort of query do I need? I tried the "Unmatched query" but this did not get the result I wanted.
Thanks for reading.....
Frank.