Chaning The Value Of A Field From An Unrelated Form
Jan 2, 2007
I have a 'Services' subform that allows a user to enter payment information, but the amount entered is over a specified amount then a field's value in another data is supposed to change (in this case, the 'Contract Status' should change from its default 'Active' to 'Void'). I was able to get an error message to appear upon this occurrence, but I have no idea how to change the value in the other table. here is my code:
Private Sub MaxComp_AfterUpdate()
If Me.MaxComp > 50000 Then
MsgBox "The specified amount is above the $50,000.00 limit. This contract will be flagged and reviewed by HCC to ensure compliance.", vbExclamation, "FMV Limit Exceeded"
'*** [sbfrmContracts]![Contracts]![ContractStatus] = "VOID"
End If
End Sub
***:This is the line I attempted to use to change the field value on the other table
I need to have a field show up in my report that is unrelated to all the other information in the report. It is a one-time entry that the person enters upon opening the database. It's basically the expected amount of work we'll be doing for the year. I need to display that at the top of my report and then utilize that number for calculations with other fields in the report to produce a couple of extra calculated fields in the report. I've tried it a few ways and I've either gotten #name? or #error.
Im a new user to access and I was wondering if someone could tell me if it is possible to put two completly unrelated tables into a form. All I want to be able to do is look at the information from both at the same time without having to switch between forms. Ill apreciate any help you can give. Thanks.
We are using acces to track and tag all incoming jobs. Since can be used by multiple users I would like a dialog box that would come up at open and tell me the file is currently being used by another user. Excel has that feature. Otherwise we keep using duplicate job numbers. Any help is appreciated.
I have a form based on Table A. When a yes/no tick is clicked in the form, I want to delete a record from Table B. The tables are joined by ID numbers.
I am trying to link two unrelated sub forms to a main form so I am able to query data all at once and make a report that displays all this data at once. I do not know if this is possible. I will tell you to the best of my ability about what I have going on.
My main form is a shift report. The primary key is a auto number ID. The rest of the fields are date, name, shift, vehicle. etc.
The first sub form is area attendance. Field are as follows auto number ID (primary key), report ID(which comes from the main form, linked), the area, and the area attendance.
The second sub form is the event log. Fields are as follows auto number ID (primary key), report ID(which comes from the main form, linked), time in, and events.
My relationship now is simply primary key from the shift report (the autonumber) going to the first and second subforms report ID's.
Problem is I can not query two distinct subforms like this (I realized).
I have three tables DriversName, Route and Orderlines. My job, if I should accept, is to create a form for the dispatcher to assign a driver and a route to each order. Preferably in a query so I can create reports based on Route and Driver, for the loaders & production, and Driver and Route for the driver.
I have a nice query already built to capture the order lines to be delivered daily, averaging around 800 lines. My problem is relationship. There is no relationship to Orders from either the DriversName or Route tables. Unfortunately the Orders table cannot be added to as the next data download would just remove the fields. I have tried just to slam in the tables in my query but of course I get a repeat data line for every driver name and Route letter.
I need this form to have the dispatcher call up the order by number and have two comboboxes for them to lookup and assign order numbers to Driver and Route. Thanks in advance for any suggestions.
My question is, does someone have a suggestion as to how I could build a relationship of Drivers and Routes to the Order query?
Hi all, I have a problem in returning values from 2 different tables because they are not related. Let me explain:
I'm trying to do "Payment Due" query by substracting the amount in the "Cost" table with the amount in the "Payment" table ([CostAmount]-[PaymentAmount]). However, since no payment has been made, the table contains no related record.
At first I thought the problem lies in null values the table return hence I tried to use NZ function to convert null to zeros. Then I realised that no values has been returned from the table due to no related record available.
This may be a simple question, but I'm not sure what to even search for. I have a form with multiple subforms. All the subforms have related fields that are linked via relationships. But one subform has no fields in common with the parent form.
I need to be able to have the subform update itself whenever the record is changed on the parent form. I can't figure out which event procedure to use. It updates correctly if I assign my procedure to the form_click sub, and then click each time I want it updated, but that's a pain. I've tried on load, on data change, on activate, on data set change, and a couple others, but nothing works automatically.
How can I make the subform run a subroutine each time the parent form changes records?
I am trying to design a report that will Give information about down time and work time during the day.
I have three tables that I am trying to query: TimeSheet - Employee name, date, etc.TimeSheetSub - Times worked (related to TimeSheet)Orders - Including Start and Complete dates and timesI want to find out on TimeSheet.Date Between TimeSheetSub.Arrived And TimeSheetSub.Departed how many minutes were worked on an order and how many many were not (Orders.Start And Orders.Complete).
When I try to combine these three tables in a query, though, it either ends with tons of records or doesn't work because the tables are unrelated. I was able to do it by making a subquery for the Orders part, but this will not work to make a report.
Any help on this matter would be greatly appreciated!
I am trying to take similar fields from unrelated tables. both fields contain information about the age of my clients. The tables they are in are unrelated, as they are different types of clients, and therefore can not find anyway to relate the tables. I need to come up with a total number of clients at particular age groups. Meaning i need to know how many clients from both tables are between the ages of 8-20, even though the records have no relation. All i need is a count, i dont need to display the records, i dont need to change anything, I just need to know how many.
I have tried to combine data from 3 different tables (unrelated) to make a new one. However, as I understood I can not do this because these 3 tables are unrelated. In my case my 3 tables are for 3 labs' material requirements. I am planning that each lab will fill a form which directly related to there field and direct to me. That is why I intend to have separate tables for each. However, I want to create a table where I will be able to see all the requirements of different Labs altogether. This new table will be kind of Orders.
I am fairly new to Access 2013 but am trying to create a query that will select random records from three totally unrelated tables and display the results together as if one table -- think video slot machine wheels. Each table has two fields - ID which is the primary key and NAME. The data in the tables are names of states, names of colors, and types of animals. Each table has a different number of records. My end result is a table that selects X number of random records from each table and displays them side by side like this:
Desired Result: Animal Color State cat red Ohio dog blue Texas fox green Iowa
I have been able to create three individual queries that will pull X number of random records by using:
SELECT TOP 10 Animals.[ID], Animals.[Name] FROM Animals ORDER BY Rnd(-(100000*[ID])*Time()); SELECT TOP 10 Colors.[ID], Colors.[Name] FROM Colors ORDER BY Rnd(-(100000*[ID])*Time()); SELECT TOP 10 States.[ID], States.[LongName] FROM States ORDER BY Rnd(-(100000*[ID])*Time());
Using the three queries above I get three separate lists. how to make one query that will randomly pull from all three tables and make the display above?
frmOperations allows the user to assign a manager to an operation by selecting the manager record from a combobox. Occasionally the user may need to setup a new Manager record if one hasn't been setup already. In this case there is a "New" "button" (it's actually a label with an on click event) that the user can click to open frmManagers and add the new manager record.
The code to open frmManagers is:
Private Sub lblNewManager_Click() DoCmd.OpenForm "frmManagers", acNormal, , , acFormAdd, acDialog Forms!frmManagers!cboMoveTo.Visible = False Forms!frmManagers!lblManagers.Visible = True End Sub
Once frmManagers is open the user creates the new Manager record and then closes the form using a similar label with an on click event:
Code: Private Sub lblClose_Click() DoCmd.Close acForm, "frmManagers", acSaveNo End Sub
frmMangers also has an OnClose event that will refresh any comboboxes on other forms that refer to tblManagers to make sure that new Manager records will be available immediately for the user to choose from:
Code: Private Sub Form_Close() If CurrentProject.AllForms("frmPlants").IsLoaded Then Forms!frmPlants!cboPlantManager.Requery Forms!frmPlants!cboQCManager.Requery
[Code] .....
So the problem comes when the user clicks the Close label (acting like a button) on the frmManagers. The code successfully closes the form and the on close event successfully refreshes any comboboxes on forms that may be open, but then for some reason it attempts to run again or perhaps continue running the onClick event that opens frmManagers. Since the form is already closed it gets hung up on trying to change the visible properties of the controls and the code fails.
My Access 2007 database with one main table. There are associated queries and reports that sort under that table when I have tables and related views checked. The problem I am seeing is some clearly related objects fall under unrelated objects and not with the table.
Short of using a custom group and sorting them manually?
A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is
Code:
dim my_var as String my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _ & " FROM Table1 " _ & " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _ & "FROM Table2 " _ & " WHERE Emp_ID = 3 "
Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )
I thank you in advance for considering this inquiry.
From within a Form.field (based on a master table query), I desire to trigger two events; one before update and one after update.
This function will serve to document specific form.field value changes to a "log" table for review prior to being committed back to the master table.
What I'm looking for are functions, which I can use in a Macro or VBA code to facilitate a field read and copy before change and a read and copy after change triggering.
I have a problem. I have a form, being used as a subform. I have an "Original Date" field and a "Revised Date" field. I want the value the user types in the "Original Date" field to be the default value in the "Revised Date" field. I used default value "=[OrigDate]" but that doesn't work, it just shows a blank. Both "Original Date" and "Revised Date" are in the subform. Is there any way I can default "Revised Date" to the "Original Date" entry (and let the users change the "Revised Date" later on) Thanks EEK
How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.
In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.
So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.
I am currently stuck on set focus property. I have a main from with nested subform. I am trying to move the focus from last field of the subform to another field on the main form.
Now I have a field name [Securedesign] in frmOrderdetails and I want the tab order to navigate to field [CustomerID] in frmAddresses which is a subform to frmCustomers.
I have my Assets form and the primary key is the ChargerID, in this form I have an "Add New Job For This Asset" button, which opens up the Jobs form at a new record.
How do I make it so that the ChargerID field is automatically filled with whatever the previous record was instead of being blank.
For example if I have Charger12345 open in the Asset form, I'd like to click the Add New Job button and it automatically have Charger12345 in the ChargerID field of the Jobs form.
Any easiest solution so that After I Update the field called JobStatus in the subform, it changes the field called JobStatus in the main form to the value which was selected from the subform?.
hi, i would like to be able to prevent a user from being able to change a field (lock a field) in a main form if a field in a subform is complete. (the field in the subform is named: "new_weekly_base" if this is complete then i would like the field: "weekly base" to be locked on the main form. is this possible?, please help.
the main form is named: "SCREEN-MAIN" the subform is named: "SCREEN-SUBFORM" (the main form has a button on it which loads the subform.)
ive tried the below code but it doesnt work, any help would be excellent.
------------------------------------------------------- Private Sub Form_BeforeUpdate(Cancel As Integer) If [NEW_WEEKLY_BASE] >= 0 Then
With Me.WEEKLY_BASE .Visible = True .Enabled = False End With End If End Sub ------------------------------------------------
