I Need To Simplify My Report_launch Forms
Jul 5, 2005
***this has already been posted on the report formus...i posted it here also due to its relation to forms***
**********************DO NOT GET MAD**************************
GOOD MORNING...
I currently have a form to launch a set of pre-desinged reports. The main function of the form is that it allows the user to sort or filter the data that appears in the report based on their selection (from combo boxes). I currenty have it set up so that the user can choose a specific client, facility, or region. The problem is the following:
THE FORM IS WAY TOOOO BUSY AND NEEDS TO BE SIMPLIFIED. EX:
CLIENT (combo box)
Microsoft
nortel
dell
ibm
.
.
.
btnLaunchRPT1 btnLaunchRPT2 btnLaunchRPT3
REGION (Combo box)
North
South
East
west
north-west
.
.
.
btnLaunchRPT1 btnLaunchRPT2 btnLaunchRPT3
etc.
each btnLaunchRPT'n' has the following code
DoCmd.OpenReport "rptECM Numbers", acViewPreview, , "[Region] = " & "'" & Me.Combo299 & "'"
but I change the [region] to whatever it is I am sorting by. Can I reduce this down to only having one set of btnLaunchRPT buttons that will detect what the user has selected and applies the proper sorts
HELP
View Replies
ADVERTISEMENT
Jun 14, 2007
So I have an access application to keep track of the status of small programmable PCBs that we use..
every time one of these pcbs is relocated or reprogrammed, an entry is made into a table in my db (tblLocation) with a unique ID for that action, the ID of the PCB (SD), the location it is going to, the new owner, the firmware version it's been programmed with and the date. Hence, multiple (dated) entries for each board are possible.
what I need to try and do is retrieve the most recent set of data from this table for every board we own... so far I have come up with a method that uses 2 queries, but I would like to combine this into one SQL statement that I can pass to the access db from an excel front end using ADO...
here's my 2 queries:
#1 (qryLastEntry) finds the date of the the most recent entry for each board:
SELECT DISTINCT tblLocation.SD, Max(tblLocation.Date) AS LatestDate
FROM tblLocation
GROUP BY tblLocation.SD;
#2 uses the result set from the above to retrieve only the most recent set of data for each board:
SELECT tblLocation.SD, tblLocation.Date, tblLocation.Owner, tblLocation.Location, tblLocation.Firmware
FROM qryLastEntry INNER JOIN tblLocation ON (qryLastEntry.LatestDate = tblLocation.Date) AND (qryLastEntry.SD = tblLocation.SD)
ORDER BY tblLocation.SD;
If there is a way to combine the 2 into one statement, that would be very useful, as although this method works just fine with access and using the built-in 'get external data' feature within excel, I'd like to be able to manipulate the SQL pragramatically (and also, I may want to migrate to mysql so a cross-platform solution would definitely be preferable in the long run).
I've had a look at using subqueries to achieve this but with no luck so far..
thanks in advance,
Bogzla
View 1 Replies
View Related
Jul 10, 2007
This query works, but is really slow. Is there a better way to write it? I have 2 tables, Card and Shipped. For all cards, I am trying to insert a record into the Shipped table if it they are not already there:
INSERT INTO SHIPPED (CardID, ShippedFrom, ShipDate, QtyShipped)
SELECT Card.CardID,'Wpg',#1/1/2007#,0
FROM Card
WHERE Card.CardID NOT IN
(
SELECT Shipped.CardID
FROM Card LEFT JOIN Shipped ON Card.CardID=Shipped.CardID
WHERE SystemID= 10 AND ShippedFrom='Wpg' AND ShipDate=#1/1/2007#
)
AND SystemID=10
View 3 Replies
View Related
Feb 21, 2008
i have created a query that solves for the difference of the debit and credit of the accounts receivables of a certain member or person..
SELECT DISTINCT (
(SELECT SUM(Amount) FROM (SELECT tblJournal.Amount, tblJournal.AccountAction
FROM tblJournal
WHERE JournalEntryNumber
IN ( SELECT JournalEntryNumber
FROM tblAccountsReceivableFromMembers
WHERE MemberID = '2008-0001')
AND AccountNumber = 120
) WHERE AccountAction='Debit')
-
(SELECT SUM(Amount) FROM (SELECT tblJournal.Amount, tblJournal.AccountAction
FROM tblJournal
WHERE JournalEntryNumber
IN ( SELECT JournalEntryNumber
FROM tblAccountsReceivableFromMembers
WHERE MemberID = '2008-0001')
AND AccountNumber = 120
) WHERE AccountAction='Credit')
) AS ['TotalAccountsReceivables']
FROM (SELECT tblJournal.Amount, tblJournal.AccountAction
FROM tblJournal
WHERE JournalEntryNumber
IN ( SELECT JournalEntryNumber
FROM tblAccountsReceivableFromMembers
WHERE MemberID = '2008-0001')
AND AccountNumber = 120
);
i need help on this so that there would only be one (WHERE MemberID = ?) to use. thanks so much! i hope to read your replies as soon as you can!
View 8 Replies
View Related
Mar 20, 2006
I have an Analysis_Database which reads tables and performs calculations in a Project_Database. There are multiple Project_Databases which reside together in a folder. To run my queries and analysis I link my Analysis_Database to each of the other databases and run my reports.
Is there a way to set up these links in a table or something to eliminate the Tools/Utilities/Linked Table Manager...etc
Any siuggestions to make this process more streamlined would be appreciated. (I can not make any changes to the Project_Database).
Thanks
View 1 Replies
View Related
Oct 13, 2005
I have a form field whose value is as follows:
=Nz(DSum("Capital","Capital_Act","DatePart('q', CVDate([Capital_Month] & ' 1')) = 1 And [projectid]=" & "'" & [projectid] & "'"),0)
Since I am using Domain lookup function in this field, it takes considerable time for this field to get populated on the form when I move from one record to another.
Can anyone tell me the syntax for me to move this expression inside the Form's Query so this can execute faster?
Please note that I need this query to be updateable as I have some other form fields that needs to be edited.
Thanks!
View 1 Replies
View Related
Mar 19, 2006
I have a table with the following fields:
Record_No <--- This is an autonumber field
Est_COE --Date Field Type
DATE_DOCR
DATE_Followup
Progress
I then have used multiple querries that just count records based off of Status:
Example of one of the querry's:
SELECT Count(borrower.Record_Number) AS Close_Of_Escrow
FROM borrower
WHERE (((borrower.Est_COE) Between Date() And (Date()+7)) AND ((borrower.Progress)="active"));
Another Example of one other querry:
SELECT Count(Borrower_Journal_Notes.ID) AS Followups_Not_Done
FROM Borrower_Journal_Notes
WHERE (((Borrower_Journal_Notes.Follow_Up_date)>Date()) AND ((Borrower_Journal_Notes.Followed_Up_Complete) Not Like -1));
Then I put both these querry's into a another querry so i can get the data put into a single form using one record source. (The master querry)
View 3 Replies
View Related
Jul 25, 2005
Well Super Complicated to me. I have setup two queries as follows,
Query 1
The function of the query is basically as follows.
1. If Field 1 is blank do nothing.
2. If Field 2 is blank then Add 7 days to Field 2.
3. If this falls over a weekend then Add another two days.
The expression I have added to do this is,
IIf(IsNull([Field1]),"", IIf(IsNull([Field2]),DateAdd("d",DateDiff("ww",[Field1],DateAdd("d",7,[Field1]),7)*2,DateAdd("d",7,[Field1]))))
Query 2
This query check whether the result of query 1 is a Weekend and if so adds another two days to it,
IIf(Weekday([Field2]= 1 Or Weekday([Field2])=7,DateAdd("d",2,[Field2]),[Field2])
My biggest problem is if I try and do any filters on this information then I get "Expression to complex" errors.
Am I over complicating things here?
Any ideas would be greatly appreciated.
JC3
View 1 Replies
View Related
Jun 29, 2006
Hi, hope someone has an idea of where to start on this.
Our company offers projects to multiple contractors and we have to enter when we spoke to them and their response. I put a textbox with default of Now(), thinking these would be recorded in real time and it would be no effort at all. But the offers are frequently entered days later, and keying in dozens of specific dates/times in the long format is very irritating for users.
Does anyone know of something similar to the DatePicker or calendar popups that will let you use arrows or comboboxes or something to quickly enter a time? We record date, hour & minute (no seconds) because it updates the contractor's order on the rotation list (i.e., next call is made to the contractor who has been waiting the longest for an offer). No ActiveX please, we're in secured network environment.
Thanks in advance.
View 3 Replies
View Related
Sep 5, 2014
Where can I find the Hex or RGB value for the blue colour that Access 2010 uses by default for buttons on forms? I need to change some buttons to yellow (I know the code for that) but later change them back to the previous shade of blue, which is shown in the Properties pane "Accent 1, Lighter 40%".
By clicking in the standard colors area at the bottom of the colour chooser I can find a very similar blue #D6DFEC but it doesn't look quite right. And the "accent" colour does not give me a Hex value.Is that "usual" blue even one colour? How can I reset a button to that style having changed it?
View 14 Replies
View Related
Apr 6, 2015
I have a bit of a problem with a database in Access 2013. On 1 of the forms, I have a number of buttons which open other forms and filter the results using an embedded macro. All has been well until a few days ago when error 2950 pop up box started appearing. After fiddling around for a while it all works OK (without actually changing anything) until next time the database is opened. I checked to make sure the location is "trusted" and all seems OK.
View 4 Replies
View Related
May 29, 2015
I have a bound continuous tabular form,However, based on data content in one field of a record, I want a checkbox in the same record enabled, so the user can check it if necessary. I have created a record set using the form as shown below, and I am looping through each record. To show that my code is referring to the field with required data content, I display it as a message box and it works, yet my checkbox does not enable.
I have the code in the form_load event, however, for testing purposes I have it behind a button.If I am seeing this properly, the code behind the button enables the checkbox for ALL records once the criteria in the required field is true, and based on the last record, which has no data content, it disables the checkbox in ALL records. I also have the PK ID for each record hidden in the form. Can I utilize that to target the checkbox of each individual record??
Form Detail
-Form does not allow additions or deletions. Edits allowed
-All fields are disabled and locked
-I only want the check box to unlock if data is found in the "RequiredField" as referred to below. I have also tried if not isNull(requiredfield.value) then -enable checkbox, which yields the same results
Here is my code
Code:
Dim rstMyForm As DAO.Recordset
Set rstMyForm = Forms!MyForm.Form.Recordset
rstMyForm.MoveFirst
Do While Not rstMyForm.EOF
If Not RequiredField.Value = "" Then
[code]...
View 14 Replies
View Related
May 28, 2013
I have created a table for installer invoicing, and have a field for invoice amount. I have created a user input form that allows a user to fill in certain pay rates for different aspects of installation, and would like to know how to make the invoice amount a calculated control that will auto update the field InvoiceAmt in my table. I can't figure out how to do this in the property sheet.
View 3 Replies
View Related
Nov 8, 2013
In the default property of a subform control I want to use a control of the main form without using names of forms, but using me and parent.
I used in default property of cboVATDetail: =Me.Parent!cboVAT, but it is not accepted. My aim is to use cboVAT of parent as default in cboVATDetail of child.
View 3 Replies
View Related
Jun 20, 2013
I have a listbox on a subform (or a subform within a subform).
When it is clicked I want all other listboxes to unselect.
There may be listboxes on the mainform, on other subforms, on other subforms of subforms.
I imagine I'm looking at a recursive function of some sort, but I'm not entirely sure of syntax to identify parent and children forms...
pseudocode so far:
loop all controls
if control = listbox, unselect all
if control = subform - recurse: loop all subform controls
if control = parent... err... Fail.
View 3 Replies
View Related
Jun 7, 2013
Making a small database, Got 1 Table.
1. ContactDetailTable
Got 3 forms.
1. ContactIDForm
2. ContactInfoForm
3. NewContactFrom
In ContactIDForm it contain 1 textbox name 'TextBox' with Button Name 'Btn'
In ContactIDForm there is only 1 Text Box ContactIDTextBox and 1 Button. User Enters ID in TextBox and On Button Click Event it should check data from TextBox in Table name (ContactDetailTable) in field ContactID and if there is record matching, ContactInfoForm should Open else NewContactForm should open with ContactIDTextBox value in it.
View 10 Replies
View Related
Jul 29, 2015
I am building a very comprehensive Membership Management Database with extremely useful facilities initiated by some 20 or 25 Queries..
The database includes 5 Tables which store data relevant to members, another which stores details of Interest Groups and a 3rd which stores which members are affiliated to which Group or Groups.
Currently the database is accessible only by a very limited number of persons and data can be entered/edited by only one person. The database, using Access 2010, is maintained on a desktop computer and synchronized to a copy on a Cloud.
My requirement is to permit some 50 Group Leaders to input/edit data on a Password protected Group Members Form such that that is the only element of the database that they can access. The Forms would have only 2 fields from which the user would select entries from drop down lists.
My query is ; is such a procedure feasible in principle and would I be correct in assuming that the user would require to have available a copy of Access.
View 7 Replies
View Related
Mar 21, 2013
I need to pull the calculated values from approximately 10 forms into another form. This is a summary form that should have all the totals pulled from the other forms.e.g. Form A has a textbox that reflects the sum of the amount. This is the total balance of form A.Form B, Form C, etc. all have a total Balance.Now, i need to pull all these totals into a summary form
-Form A Total Balance: x
-Form B Total balance: y, and so forth.
How and what is the best method to approach?I have tried using Forms![Formname]![Total] to get the data. This necessitates the need to hide all these forms and I ended up with blank forms, etc.Even so, the total sometimes appear and sometimes it does not. so it is very unstable.
View 11 Replies
View Related
Jun 5, 2015
I have a form "NewMetalF" that has three combo boxes. The first one is used to choose "Precious Metal" or "Base Metal". The second one shows all the metals based on the first combo box. When I open the form in form view alone the combo boxes work flawlessly. I run the form "NewMetalF" within another form "NewExternalPartF" by clicking a button "AddMetal". When the button is clicked "NewMetalF" opens. The first combo box is selected, and when the second combo box is clicked the error "Enter Paramater Value" "Forms!NewMetalF!cboPreciousOrBase".
View 13 Replies
View Related
May 22, 2015
Is there any way to mix together on a db based on tabbed documents some overlapped forms?
View 4 Replies
View Related
Apr 11, 2013
New to the navigation form: I need several forms in the navigation form to all relate to the same client ID field. I have this information on the main form but the form with in the navigation form will not pick it up.
I have tried =[Forms]![Frm Client Information Navigation Panel]![ClientID] But just cant get it to pick up that information...
I would like to have something like the master link fields and, child link fields but when I add a form to the navigation form this option in not available.
View 1 Replies
View Related
Dec 26, 2014
I am creating a simple data entry form wherein the user will enter the product id and on change the product description and retail price must be displayed.
When the order quantity is entered, the total cost need to be calculated. I am able to do this using DLookUp and simple multiplication.
However, after doing the above, I need another row to appear so that I can accomplish the same for another product.
View 2 Replies
View Related
Dec 8, 2014
I am developing a small database for use in a multi-user environment: I have split the tables off and am testing with two copies of the front-end on different machines. All seems to work well except on one form.
On this form, the value of a bound control (exposed as a number of radio buttons) is used to make related controls on the form visible or invisible. I use the AfterUpdate event to trigger appropriate VBA code to accomplish this; it works well.
However if two users have the same form open at the same record (unlikely, but not impossible), and User A changes the value of the control via the radio buttons, Access will eventually propagate the change to User B's copy of the form but the AfterUpdate event will not be triggered and the form becomes inconsistent. I understand that events are only triggered when a user makes a change to a control; how can I be alerted when Access makes a change?
View 4 Replies
View Related
Nov 26, 2013
Two forms have matching fields (ContractID), and each form has different data displayed from different tables (SQL views, actually).
I want Access 2007 to stay on the same ContractID when the user switches to a different form.
View 4 Replies
View Related
Apr 13, 2013
I have an access form with tab control pages each with embedded forms.In one page I have a list of records as a datasheet form with the record identifier field configured as a hyperlink. When I click on this it passes its value to a form field in the form in the following tab page (works without the hyperlink but its a useful way of highlighting which field to click) which is a display/edit form of the details of the individual record. The Subroutine which does this sets the focus on this field it is passing the value to on the other form and the 'On Got Focus' event in that triggers the query that fills the editor form.
When I click back on the following tab to select another record to view/edit, the identifier value of the other record is passed to the other form but the data displayed in the rest form doesn't change. I understand this is because the field with the 'On Got Focus' never lost focus and so the query function was not called. To correct this I tried adding the same function call to the 'On Change' event however this does not work (the field is a text box set to 'locked' if that is relevant).I can get the form to do a new query properly if I click on a different field in the form before going back to the list tab or (as this is removing the focus allowing the On Got Focus to work again) if I click on a button to select a different record (which just takes me back to the list form).
Is there any way I can get this to work without having to manually remove the focus from the problem field? I have tried setting its On Got Focus event to move the focus to another field after the query function call however this prevents the Setfocus line in the code in the previous form from working. Here is a snippet of my code below.This code is for the list form and passes a value to the editing form:
Private Sub SalesID_Click()
Forms!Mainform.Requery
Forms!Mainform!Sales_Admin_Form!Sales_Admin_SalesI D.Value = SalesID.Value
Forms!Mainform!Sales_Admin_Form.SetFocus
Forms!Mainform!Sales_Admin_Form!Sales_Admin_SalesI D.SetFocus
[code]....
View 1 Replies
View Related
Aug 5, 2015
I've split my database into a front end and back end which was relatively easy. I did this so that multiple users can have their own front end. But if I have to go in and change the back end and front end, does that mean I have to remake all the front ends? Right now I'm finding that if I delete my linked tables on the front end and then import them into the back end again and change everything on one screen and then split it again is the only option I know of.
View 6 Replies
View Related
