Modules & VBA :: Subquery Where Subform Is Shared Between 2 Forms

Mar 28, 2015

There are 2 mainforms and both of them share a subform. This subform is an add subform and I want the mainform to be refreshed after the data entry.Prior to the sharing, I had it coded it the way below and it works fine

Code:
Forms!frmMainformnoproc!frmActiveTasks.Requery

How would I recode this requery when it shared between 2 forms. frmMainformnoproc is the name of one of the main forms.The code above was present after an update was completed in the subform. Maybe I should be doing it after I close the subform and refresh the mainform. Based on that line of thinking I tried to add the code in the Activate event / Got Focus event of the mainform but the form did not get refreshed.

View Replies


ADVERTISEMENT

Forms :: Cascading Combo Box On A Shared Subform

May 30, 2013

I have a subform that I use on two parent forms. The subform has a cascading combo box. The LocationID field gets filtered after CompanyID (named: cboCompanies) is updated. The problem is that I can only get the combo box to work on one form (Contacts_All). When I open the second form (Contacts_Clients), it asks for the Parameter Value.I am entering this into the criteria for the LocationID field:

[Forms]![Contacts_All]![SUBFRMContactsCompanies]![cboCompanies] Or [Forms]![Contacts_Clients]![SUBFRMContactsCompanies]![cboCompanies]

So, the "Or" is not working.Do I need to make a separate subform for each parent form or is there some way to get this working? I already feel as if I am managing way too many forms and need to simplify.

View 1 Replies View Related

Subquery In A Subform

Nov 23, 2006

Hi,

I am trying modify my forms so that I can display data like a tree view. I have a mainform and a subform. The source of the subform is a datasheet view of a query which has children linked to it. I have attached a snapshot view to this post.
How do I access a field in the child in the subform (it is not a subsub form - it is a subquery within a subform)? (Field need to be accessed highlighted in black in th snapshot)

Thanks
PH

View 1 Replies View Related

Queries :: Referencing SubQuery Fields In A SubQuery

Dec 3, 2013

We are developing a complaints tool. Each completed complaint needs to be signed off by 3 leads and I'm hoping to display the progress of this in a form. Obtaining the first is simple and I was able to do that relatively quickly. The subsequence ones are now giving me a headache as it doesn't seem I can reference the initial subquery field in the others.Here's what I have so far:

Code:
SELECT COMPLAINT_TBL.COMPLAINTID, COMPLAINT_TBL.CASENUMBER, COMPLAINT_TBL.COMPLAINTANTFORENAME, COMPLAINT_TBL.COMPLAINTANTSURNAME, BUSINESSUNIT_TBL.BUSINESSUNIT, COMPLAINT_TBL.FINALRESPONSEDATE,
(SELECT TOP 1 [SIGNOFF_TBL].[SIGNOFFDATE] FROM [SIGNOFF_TBL]
WHERE [SIGNOFF_TBL].[COMPLAINTID] = COMPLAINT_TBL.COMPLAINTID
ORDER BY [SIGNOFF_TBL].[SIGNOFFDATE] DESC) AS FIRSTSIGNOFF,

[code]....

View 6 Replies View Related

Modules & VBA :: Add Appointment To Shared Outlook Calendar Using Access

Aug 19, 2014

I am very new to VBA. I found some code that will allow me to add an appointment to my personal calendar with using access VBA and it works great. It also allows me to add an appointment to another calendar created under "My Calendars" folder.

I wanted to know how to change the code in order to add the appointment to the SHARED calendar instead.

The main folder is called "Shared Calendars" which is at the same level as the default "My Calendars" folder. Under the "Shared Calendars" folder the calendar is called "Tester".

Here is the code im using at the moment: I believe I have have to change the line of code that is in red but I don't know how.

Private Sub cmdAddAppt_Click()
On Error GoTo cmdAddAppt_Err
Dim outobj As Outlook.Application
Dim outappt As Outlook.AppointmentItem
Dim olNS As Outlook.NameSpace

[Code] ....

View 1 Replies View Related

Modules & VBA :: Search And Pull Data From Db Stored In A Shared Path

Dec 30, 2014

I have two different database files. One is 2010 ".accdb" format where I have created a form and the inputs to the form is getting saved as records to an access.mdb file in a shared path.Now if the users want to edit the existing record I should allow them to search their previously submitted record with a unique ID number.

I know it is possible when we have both the form and table in the same db. But I want to know whether it is possible to search with a unique ID and pull the data from different db in a shared path using a command button?

View 5 Replies View Related

Modules & VBA :: Browse For A File On Shared Drive And Link It To Record

Feb 24, 2015

I have a form displaying records. I would like the user to be able to select a button, browse for a file on a shared drive and link it to that record.

I have been exploring hyperlinking, which works for the most part, although I need it to display the share name rather than the drive assignment for that user. Such as drive1folder rather than C:folder.

Hyperlinking also unfortunatly requires the user to right click on a field select edit hyperlink then browse.

View 7 Replies View Related

Passing Variables Between Forms In Shared Mode

Sep 28, 2006

Before I go any further w/this current application, I want to make sure this will work.

I have an access database w/a few forms that will sit on a shared drive on a network. Each user will also have their own username and password because each user will have a certain level of access to what they are allowed to see. The problem I am facing is that if I put the users ID into the global variables module, and two users log on at the same time, then access seems to somehow use both of the IDs when running queries. This makes sense that all uses can see it, since it's Global... but I need a way for simultaneous users to have a persistant unique id so I can query data that's only meant for them. I found this example of code that might remedy the situation by passing variables between the forms.

Call the code below in frmOne to pass the variable.

DoCmd.OpenForm "frmTwo", acNormal, , , , acWindowNormal, "Count=2"


Call the code below in frmTwo to get the variable.

Dim i as Integer
i = CInt(GetTagFromArg(Me.OpenArgs, "Count" ))



Will I end up w/the same result? Will all users be able to see this variable as well?

View 6 Replies View Related

Forms :: Split Database In A Shared Folder

Nov 9, 2014

I have recently been experimenting with adding an Access database to a shared folder (in a network HomeGroup) and have it working on two networked PCs. Then tried the same with the database split but I get a path error on the PC joined to the HomeGroup (where the database resides) whenever the backend (tables) are accessed by a form or from the Tables listing. If I however open the backend file directly from the file directory, the tables all open as expected.

The path stated as being invalid in the error message starts with "C:Usersetc..... instead of LAF-PCUsersetc..... so it looks (to me at least) that it is attempting to load the file from the local hard drive rather than the host computer.

Why a split database works differently in this situation than a non-split.

View 13 Replies View Related

Forms :: Difference Between Shared And Embedded Picture Type?

May 29, 2013

For my database, I would like a picture as my background but I don't know if I should use embedded or shared?

View 2 Replies View Related

Help With Subquery

Feb 12, 2007

Can anyone tell me what is wrong with this complex query? It works fine without the highlighted subquery. The subquery works fine on its own. But they don't work together.

I think that I might not be able to call multiple results from a subquery but I'm not sure about that. Does any one know???

Any help will be much appreciated!
Thanks!
select k.site_id, s.name, t.date_opened,

(select concat(c.name_first,' ', c.name_last) as User from sitepack_approval sa
left join sitepack_approval_users spa on sa.approval_id=spa.approval_id
left join contacts c on spa.user_id=c.contact_id where sa.site_id=k.site_id and spa.timestamp is null
order by spa.level limit 1) as pending_approver,

(select sat.name from sitepack_approval sa left join sitepack_approval_types sat
on sa.approval_type_id=sat.approval_type_id where sa.site_id=k.site_id order by sa.approval_id desc limit 1)
as sitepack_type,

(select t.site_id, D.name AS Division, R.name AS Region, M.name AS Market,
concat(C1.name_first, " ",C1.name_last) AS RD,
concat(C2.name_first," ",C2.name_last) AS DD
FROM stores as t LEFT JOIN regions as M ON t.region_id = M.region_id
LEFT JOIN regions AS R ON M.parent_region_id = R.region_id
LEFT JOIN regions AS D ON R.parent_region_id = D.region_id
LEFT JOIN contacts AS C1 ON R.contact_id = C1.contact_id
LEFT JOIN contacts AS C2 ON D.contact_id = C2.contact_id) as site, Divsn, Reg, Mkt, RDs, DDs,

concat(cd.name_first,' ',cd.name_last) as 'DRE'

FROM sitepacks k LEFT JOIN sites s on k.site_id=s.site_id
LEFT JOIN stores t on k.site_id=t.site_id
LEFT JOIN contacts cd on k.dre_contact_id=cd.contact_id
WHERE status_id=(select status_id from sitepack_status where description='Approving' and sitepack_type <> 'Renewal')
and t.status_code <> 'DEAD'
and t.date_opened > now();

View 1 Replies View Related

Subquery Problem

Nov 28, 2006

Hello
I have had a bit of a stumbling block with a subquery for a Daily production report and I was hoping someone could advise me.

An initial query presents the information as the table below.

Date ProdNoFirstOfSerialNoLastOfSerialNo Qty
13/11/2006004006P064600254P06460028128
13/11/2006004006P064600282P06460030019
13/11/2006004105P064600301P0646003033
13/11/2006004006P064600304P06460031714
13/11/2006004006P064600318P06460034427

However what I need to have is the table actually look like this,

Date ProdNoFirstOfSerialNoLastOfSerialNo Qty
13/11/2006004006P064600254P06460030047
13/11/2006004105P064600301P0646003033
13/11/2006004006P064600304P06460034441

I had thought to try and make another query identify the change of product number to then set the first and last serialNo's but I have no idea on how to write the statments necessary.
If anyone could offer some advice it would be very much appreciated.

Regards
Henson

View 3 Replies View Related

Subquery Issue

May 15, 2007

Hi Chaps,

I haven't used subqueries before, so I am not 100% on the best way to approach this.

The Scenario:

I have tables;

tblJobDetails
tblMissingKit
tblSiteDeficiencies

JobID is the primary key for tblJobDetails. tblMissingKit and tblSiteDeficiencies are both tables containing (funnily enough) Missing Kit and Site Deficiences on the Job's (JobIDs) in tblJobDetails. The problem is, there can be more than 1 amount of missing Kit per JobID and more than 1 site deficiency per JobID.

In an ideal world, when I run this query, I would only get 1 record per JobID, but in a 'MissingKit' field, there would be a list of the missing kit for that site (new lines, comma seperated - doesn't matter) and then the same with Site Deficiencies.

Not sure if this is even doable, but thought I'd ask the people who know.

Thanks in advance,

Taz

View 1 Replies View Related

Parameter In A Subquery?

Jun 11, 2007

Hello,

I've got two questions:

1) Can you use a parameter in a subquery?

2) Can the parameter value be the value from another field in the query?

In a nutshell, I want to write a query that returns these fields about some marbles:

Color of Marble
Marble Stat1
Marble Stat2
Marble Stat3
Number of Marbles of this Color

i.e. desired query results

Blue Stat1 Stat2 Stat3 24
Red Stat1 Stat2 Stat3 12
Green Stat1 Stat2 Stat3 38

The query is fine except for the last field, Number of Marbles of this Color. This field is a subquery with a parameter that is the color of the marble. So if I run the subquery on its own and pass 'blue' as the parameter, it returns 24.

Here is the SQL for the subquery, qry_Count_Marbles:

SELECT Count([Marble_ID]) AS [Count]
FROM tbl_Marble_Info
WHERE (((tbl_Marble_Info.COLOR)=[Marble_Color]));

Marble_Color is a parameter, and If I enter 'blue' it returns 24.

In my main query, I am trying to pass the parameter for the subquery as the result of the first field. It's not working, but here is my SQL:

SELECT tbl_Marble_Guidelines.COLOR, tbl_Marble_Guidelines.LOW, tbl_Marble_Guidelines.HIGH, tbl_Marble_Guidelines.FUND, qry_Count_Marbles.Count(tbl_Marble_Guidelines.COLO R)
FROM tbl_Marble_Guidelines, qry_Count_Marbles


I'd like the main query to not prompt the user for the parameter, but simply grab the parameter value from the value of the first field. Is what I'm trying to do possible? Is there a way to pass a SQL parameter within the SQL itself, and not prompt the user?

Any ideas?

Thanks,

Scott

View 1 Replies View Related

How To Do A Subquery Or Join?

Jun 6, 2005

Ok at the moment, my sql statement works. However the return results is not arranged according to the earliest time. I've set DATATYPE for my Time as TEXT instead of DATE/TIME. This is because of other limitations in my forms.

Ok, that aside. This is how my SQL looks like:

Code:Select DISTINCT Time from Sample where Lot_No = 'BB55555' AND Product_Code='1234567'

Is there any way where by I can include Sample_id so that I can sort the Results using Order by Sample_id ASC?

i tried this but the DISTINCT doesnt work anymore.

Code:Select DISTINCT Time, Sample_id from Sample where Lot_No = 'BB55555' AND Product_Code='1234567' ORDER BY Sample_id ASC

View 1 Replies View Related

Subquery Works Alone, But Not With Query.

Oct 4, 2006

Hi,

I have written a subquery that works fine alone, but it returns -1 when with query.

The subquery is supposed to return a total of type currency.

Any help/suggestions will be very much appreciated.

SELECT tblSite.Name, tblPhase.Phase_No, tblVariation_Order.Customer_No, Exists (SELECT Sum(tblVariation_Order.VO_Price)
FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Custome r_No
GROUP BY tblCustomer.Customer_No;) AS Expr1
FROM (tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No = tblVariation_Order.Customer_No) INNER JOIN (tblSite INNER JOIN (tblPhase INNER JOIN tblHouse ON tblPhase.Phase_No = tblHouse.Phase_No) ON tblSite.Site_No = tblPhase.Site_No) ON tblCustomer.Customer_No = tblHouse.Customer_No
WHERE (((Exists (SELECT Sum(tblVariation_Order.VO_Price)
FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Custome r_No
GROUP BY tblCustomer.Customer_No;))=True));

View 5 Replies View Related

Problem With Subquery And IN Operator

Jan 28, 2007

well here's my problem, i've been working on this homework for about 3 days i got most of my queries work except for the 2 that use nested or subqueries,
i have 2 tables
http://img251.imageshack.us/img251/4623/untitledko5.png
everytime i run the query i get this
http://img251.imageshack.us/img251/7645/untitled2rq0.png
i want the query to show me the costumers that have not made any orders, basically it should show me ID #6 no order,

when i use
SELECT Costumer.ID, Costumer.fname, Costumer.lname
FROM Costumer INNER JOIN [Order] ON Costumer.ID = Order.tblCostumer_ID
WHERE (((Costumer.ID) Not In ([Order].[ID])));

it returns the above pic of a blank query

this is what my POS book says i should type:

SELECT Costumer.ID, Costumer.fname, Costumer.lname
FROM Costumer
WHERE ID NOT IN (SELECT ID FROM Order);

but everytime i try that i get a syntax error after the NOT IN.

i don't know what else to try if anyone could help me out it would be greatly appriciated.

View 3 Replies View Related

Format Subquery As Percent

May 22, 2007

hi all,

i normally just go into design view in my query to change the format type, but some of my subqueries are quite large, so i'm getting 'the expression exceeds the 1,024 character limit for the query design grid.

i figured i could just multiply the subquery by 100, but the results are still calculated out to about ten decimal places.

so... how do i format a subquery directly in sql to be a percentage with two decimal places?

thank you,
mj

View 2 Replies View Related

Trying To Replace DSums By Subquery

Oct 25, 2007

Hi there,

THE GIVENS:

The following query works fine:
SELECT tblProjectSteps.ProjectID, tblProjectSteps.StepID,...,
NZ(DSum("Total","tblPayments","ProjectID=" & [ProjectID] & " AND StepID =" & [StepID],0)+
NZ(DSum("Total","tblReceivables","ProjectID=" & [ProjectID] & " AND StepID =" & [StepID],0)+
NZ(DSum("Total","tblBankPayments","ProjectID=" & [ProjectID] & " AND StepID =" & [StepID],0) AS TotalCosts
FROM tblProjectSteps INNER JOIN tblSteps ON tblProjectSteps.StepID = tblSteps.StepID;

The 3 summed tables (tblPayments,tblReceivables,tblBankPayments) do all of course have a foreign key for ProjectID (from tblProjects) and StepID (from tblSteps) and the output would be something like

STEPID.....ROJECTID....TOTALCOSTS
1.............100.............1100
2.............100.............1200
3.............100...............900
4.............100.............1300
1.............200...............700
2.............200.............1400

and so on...

THE PROBLEM:

For performance issues I would want to eliminate the DSum part, i.e get me a TotalCosts field by other means . I tried several constructions with multiple queries and also subqueries but to no avail. Any suggestions from the query Cracks her would be greatly appreciated.

Thanks
Jaime,

View 9 Replies View Related

Update With Join/subquery

Apr 21, 2008

Hi, we are currently working on a project to strip down one of our SQL Server based applications to an Access 2007 based solution, and I met this problem: the query just doesn't work on the new Access mdb. Can anyone take a look and give some help? Thanks a lot.

UPDATE A SET MaxPrice= B.MaxPrice FROM
Summary A
INNER JOIN
(SELECT Name, MAX(DailyPrice) AS MaxPrice FROM Data GROUP BY NAME) B
ON a.Name = B.Name

View 1 Replies View Related

Default Value Property Subquery

Oct 20, 2005

Is it possible to use a subquery as a default value property setting?


I need to use a value from a table that is not the focus table of the form, I have tried to identify it with a subquery but it returns a #name? error every time. I hate those!

Any help would be appreciated.

View 5 Replies View Related

Order By Calculated Subquery

Jun 16, 2005

hi,

i have this "team" table with the following fields:

teamid, teamname
------------------
1, team1
2, team2

"game" table:

gameid, gamename
------------------
1, game1
2, game2


and "winlose" table:

id, gameid, teamid, winlose
---------------------------------
1, 1, 1, win
2, 1, 2, lose
3, 2, 1, win
4, 2, 2, lose

i want to generate a report in asp where each team have a total of win, total of lose and points calculated by : (totalwin*2)+(totallose*1)

teamid, Name, totalwin, totallose, pts
1, Team1, 2, 0, 4
etc...

the following query works pretty much well:
SELECT teamid, teamname,
(select count(winlose) from winlose where team.teamid=winlose.teamid and winlose='win') as win,
(select count(winlose) from winlose where team.teamid=winlose.teamid and winlose='lose') as lose,
(win*2)+(lose*1) as pts
FROM team
ORDER BY teamname

but the problem arises when i want to sort the report based on the "pts" which is the result of calculated two subqueries ("win" and "lose"), because i can't use the "ORDER BY pts" as access cannot order records by alias. can anybody find another sql solution to this?

theoritically i can just put all the records in an array and sort it by pts field, but sorting multidimensional with numerical and text array in asp kinda flakey at best, so i hope i can find the solution in the sql. thank you all in advance.

View 4 Replies View Related

Subquery To Find Previous Value

Jun 20, 2013

I have created this sub query:

Code:
(SELECT SUM(Cash)
FROM qryAccountSetup AS T2
WHERE T2.ClientID = [qryAccountSetup].ClientID
AND T2.MonthlyDate < [qryAccountSetup].MonthlyDate) AS PriorCash,

How do you change this to allow multiple values. I looked up subqueries online and it said to nest a second sub for the where clause....

Code:
(SELECT (Cash)
FROM qryAccountSetup AS T2
WHERE
(Select (SELECT MAX(T3.MonthlyDate)
FROM qryAccountSetup AS T3
WHERE T3.ClientID = qryAccountSetup.ClientID
AND T3.MonthlyDate < qryAccountSetup.MonthlyDate)) AS PriorCash,
But i keep getting error.

What i am trying to do is get the previous cash value.... For now, a good indicator is MonthlyDate, as only one date per client will exist. Something can only happen once in a day....IE.

ClientID..MonthlyDAte
1...........03/01/2013
1...........31/01/2013
1...........01/02/2013
1...........and so on.

There can be multiple dates, however the clientID would not match then.... Does this make sense? There is also a PositionID... This is an autonumber field in its respective table.. I have that so i can use it for domain purposes in the future.

View 14 Replies View Related

Complicated Table Join With Subquery

Jun 29, 2006

Hello,

This db I'm working on tracks the latest revisions of drawings. Multiple drawings fall under a work package, and multiple revisions of a drawing are present (listed 1, 2, 3, ...). I want to pull information about the latest revision of a drawing, including the drawing number, the tracking ID from the tbl_wkpkg_dwg_rev table, and the corresponding dwgTitle from tbl_dwg. The tables are joined through the field wkpkg.

I have my select statement partially working; it works on one table. This is what it returns:
dwg1 3 id1
dwg2 1 id2
dwg3 5 id3

Here is the statement:

SELECT r.dwg, r.revision, r.trackingID
FROM tbl_wkpkg_dwg_rev r, (SELECT MAX(revision) as maxrev, dwg FROM tbl_wkpkg_dwg_rev GROUP BY dwg) MaxResults
WHERE r.dwg = MaxResults.dwg
AND r.revision = MaxResults.maxrev;

So I would like to join it to tbl_dwg and get:
dwg1 3 id1 DrawingTitle1 wkpkg1
dwg2 1 id2 DrawingTitle2 wkpkg1
dwg3 5 id3 DrawingTitle3 wkpkg2

However, when I try and join it with tbl_dwg to get the other information, it returns this:
dwg1 3 id1 DrawingTitle1 wkpkg1
dwg1 3 id1 DrawingTitle1 wkpkg1
dwg1 3 id1 DrawingTitle1 wkpkg1
dwg1 3 id1 DrawingTitle1 wkpkg1
dwg2 1 id2 DrawingTitle2 wkpkg1
dwg2 1 id2 DrawingTitle2 wkpkg1
dwg2 1 id2 DrawingTitle2 wkpkg1
dwg2 1 id2 DrawingTitle2 wkpkg1
dwg3 5 id3 DrawingTitle3 wkpkg2
dwg3 5 id3 DrawingTitle3 wkpkg2
dwg3 5 id3 DrawingTitle3 wkpkg2
dwg3 5 id3 DrawingTitle3 wkpkg2
dwg3 5 id3 DrawingTitle3 wkpkg2

I tried using SELECT DISTINCT, with no luck. I'm out of ideas, so any help would be appreciated! :confused:

Thank you!

View 2 Replies View Related

Queries :: Distinct Count In Subquery

Jun 3, 2015

Extended Cost] and salestran.[Extended Price] for each salestran.[SKU Code/Number], Count the number of unique customers, salestran.[Customer Code/Number], per SKU for any transactions equal to or after salestran.[Transaction Date] 11/1/2014.

Problem is, doing a regular count on the Customer field returns an incorrect value. It counts the number of times the sku appears within the given date range, ie 6 transactions with 2 customers, my count says 6. I need a distinct count on the customer, for the above example I want to see 2. Here is the code I have so far which yields an error of "At most one record can be returned.." distinct count in my Select statement along with the other fields I want to see, ie Summary data and SKU.

SELECT Salestran.[SKU Code/Number], (SELECT COUNT(cd.[Customer Code/Number]) AS Count FROM (SELECT Distinct [SKU Code/Number], [Customer Code/Number] FROM Salestran) as cd GROUP BY cd.[SKU Code/Number]) AS [Number of Customers]
FROM Salestran
WHERE (((Salestran.[Transaction Date])>=#11/1/2014#))
GROUP BY Salestran.[SKU Code/Number];

View 4 Replies View Related

Queries :: Max Date Selection - Subquery?

May 23, 2013

I need to select a record with the latest (max) date along with associated values that go along with that record. However, some records have the same date. Initial query sorts by Asset_ID and Date (Desc)...A second query against the initial query then groups by Asset_ID, taking the Max Date and First Switch and Port...Results show the latest date but with a different Switch and Port

Asset_ID | Date | Switch | Port
123 23/05/2013 WAR01 GI01
123 23/05/2013 SAM01 GI02
123 20/05/2013 ROC01 GI03
123 21/05/2013 CHR01 GI04

e.g I get 23/05/2013 with ROC01 | GI03...I have tried doing this as a nested query but get similar results.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved