Hey guys,
I have two tables that share a common field. Is there a way to allow Access to update both at the same time? If I change one in Table1 it does not automatically update the same field in Table2. Is this possible?
Hello all...
I have a problem with a single query I have build in Access 2000. Basically, I have a normalised database structure to 3NF. I have a table called Data, a table called Reports and a table called DataReports which cotains the primary keys from both data and reports which are named Data_ID and Report_Id.
Basically, what is happening is I am building a query that returns a record from DataReports using the Data_Id field as criteria as this is held on a form. I create a one to many relationship from the Reports table to the DataReports table using a link from one Report_Id to the other. I save the table and it works fine...once only!! The query is used to feed a list box on a form. The form loads correctly but when I go back into design view to make a change to something else on the form and then go back to form view, I get Data Type Mismatch error. When I look at the query, Access has changed the link from the Report_Id --> Report_Id to Report_Id --> Report_Title which is the next field down in the Reports table. Because all ID's in my database are Autonum's it is trying to link a number to text so I understand why this would not work but I dont understand why on earth access is changing my links. It is very frustrating. Could anyone help me please.
Thank you kindly..
Gareth
Hi everyone :D
A friend just came to me with an Access related questions that has me curious. I think that I have seen what he is trying to do before but couldn't puzzle it out myself.
Here's the basics, he has two items on his form, the first is a drop down box and the second is a text box. The table that these two items are connected with has 3 fields (auto number, ARS, and description).
What he wants to do is link the two items on his form so that when an item is selected from the drop down box (from the ARS field) it will auto-fill the text box with the information from the description field of the same record.
Hopefully I explained this clearly. Can anyone out there be kind enough to provide direction on how to do this?
Any help would be appreciated!!!!!
Hi all,
I have split a database, then realised that I needed to change two fields in a form. Y/N field and Text field.
I went into the backed database changed the fields to be Required but in the front end it doesn't seem to work? I haven't tried re-linking them but should I need to?
thanks
I have two forms linked by a field "JUVIS". My problem is that I can't get the second form to show data newly entered on the first form without moving to another record on the first form. I have a command button on the first form that
opens the second form. All works well, meaning the forms are synchronized,based on the JUVIS field, except in the case of entering new data. In this case the second form opens to a blank screen instead of showing the JUVIS field. The forms are both linked to tables not to queries. Any help would be appreciated
Hi, I am trying to make a list box that shows the first name of different people in the same family in a subform. I have the first names in different fields linked to the same ID, and the list box I keep making that sort of works will show the whole row of names (but also everyone else's first names down the list), and it will only keep the first name in that row when I select it. I would like to know how to make a list box that just shows the 2 or 3 first names linked to the last name or ID that is currently on the main form I am looking at.
Thanks,
Rod
I am linking a table from another database, and for some reason a few columns are not showing up on the front end.
Access 2007
Hi everyone,
I was wondering if this is possible to do:
I have a Form that reads from one Linked Table and Updates a Local Table. Is it possible to move the Local Table to the Back End and still be able to append fields and change field names etc?
If anyone can help, that would be greatly appreciated. Thank you in advance.
Bear
I have a form [Art_DepartmentFilter] that has a subform [Art_ByRoomSubform]. The linked master/child fields are DeptID and RoomID.
There is a list box on the form that allows you to select the department, and this also updates a list box that shows the rooms in that department that have art in them.
I would like the user to be able to filter the form to show either all of the art in that department, or just the art in the specific room. I have two separate buttons, one for each filter (cmdDeptFilter, and cmdRoomFilter).
I realized I had to add the link for the RoomID in order to get the filter for the room to work (which it does nowoCmd.ApplyFilter , "[qry_artbyroom]![DeptID]=[forms]![art_departmentfilter]![textdeptid] and [qry_artbyroom]![roomid]=[forms]![art_departmentfilter]![textroomid]").
However, DoCmd.ApplyFilter , "[qry_artbyroom]![DeptID]=[forms]![art_departmentfilter]![textdeptid] and [qry_artbyroom]![roomid] like '*'" returns just the first room listed for the department, not all of the rooms.
How can I show all of the rooms for the department selected?
I am currently working on a main form in Access 2010 which includes quiet a lot of fields, therefore i choose to create parts of it (which are as well optional, as they do not apply to all records) as different forms which i linked to the "mother" form afterwards through a checkbox by using the following code:
Private Sub chkMajor2_Click()
Dim strformname As String
If Me.chkMajor2 = True Then
strformname = "Major 2"
DoCmd.OpenForm strformname, acNormal
End If
[code]....
to make the next form visible for selection after filling in the current one...something like an "add more.." field actually, which i choose to represent as checkbox.The issue that i encounter is that when i click the "Add New" button in the main form, it doesn't clear also the fields in the linked forms. Is it possible to do that with a VBA code? Or how should i proceed?
The current code that i have for the button is:
Private Sub cmdNew_Click()
DoCmd.GoToRecord , , acNewRec
End Sub
I have a database consisting of three linked tables, with ID as unique identifier (primary key).
I would have expected the data type for my unique identifier, especially in the main table, to be Autonumber. However, I discovered with some surprise that the primary key (ID) in
Table 1 (Main table) is data type: number, field size: double
whereas in table 2 and table 3 the ID is data type: autonumber (field size: long integer)
It must have happend "SOMEHOW" when I divided the original single table.
Is this as it should be? Or could it lead to trouble later on?
If not as should be, is there anything I can do right now to rectify the situation with risking upsetting the relationships and all?
What precautions should I take, apart from taking a backup?
Thanks for your help.
Adrian
I have three tables which I have linked with relationships. The linked fields are given the ID number of the parent, where I wanted the value. What on earth am I doing wrong, something stupidly obvious I bet! Access 2007
View 3 Replies View RelatedI'm having trouble with using a where statement linked to fields when the field is empty. I need a way to say if field is null then 'do nothing'/'select all' else use the text from the box.
I have a form (ServicesRCSSearch) which has 3 combo boxes (Location1, Location2 and Location3). These fields are linked to a query. The button on the form generates the query.
My SQL for the query is currently:
Select Services.Key, Services.Location, Services_1.Location, Services_2.Location
From Services, Services_1, Services_2 (copies of the same table all left joined)
Where
((IIf(forms!ServicesRCSSearch!Location1 Is Null,"",services.Location=forms!ServicesRCSSearch! Location1))<>False)
And ((Services_1.Location)=IIf(forms!ServicesRCSSearch !Location2 Is Null,forms!ServicesRCSSearch!Location1,forms!Servi cesRCSSearch!Location2))
And ((Services_2.Location)=IIf(forms!ServicesRCSSearch !Location3 Is Null,forms!ServicesRCSSearch!Location1,forms!Servi cesRCSSearch!Location3))
This works in that it uses the fields to filter the query but when Location1 is empty there are no results as you can see from the code.
I have a query linked to some combo/txt boxes on a form so the user defines which categories he/she is looking for then presses button and query comes up with relevant results. This works fine if the user inputs information that is actually there, but if they leave it blank it goes all wrong.
The statements I have in the query are as follows:
[Forms]![frmStockControlSigned]![comboSupplier]
[Forms]![frmStockControlSigned]![txtPurchaseOrder]
etc.
I thought the solution may be along the lines of this but it does not work:
=if([Forms]![frmStockControlSigned]![txtDate]=Null,[Forms]![frmStockControlSigned]![txtDate],"*")
basically i want to make it so the user does not have to enter criteria into evey box, thus if every search box is left blank it would show all records "*"
Right what im after sounds complicated but its quite simple.
I have a client database, in this database each client has 3 skill fields, i also have a form with a listbox and an unbound textboxes that are linked to the query so when the textbox is typed in the listbox updates.
Now ive sorted out the problem of not all fields showing it some of them are blank now the problem is.
When input text into one of the boxes the listupdates, now if theres text in two boxes. and theres data in one of each that matches it will show neither, because the query is looking for data that matches in both fields, now i need partial matches so it will give results of data in both fields.
not understanding fully what i mean?
i have zipped my db, if you open the db then open the form 'MainListRecTrades2nd' and fill in data in both primary and secondary trades that matches from the list you will see.
http://awilderbeast.com/CWSystem.zip
The solution i was thinking of would to have 1 textbox that searches all the fields and displays data from any of the fields in the list
Thanks for any help
I have form with an unbound combo box which is populated from a query
It should populate a field in a table which is related to another - however it only works when i query by id number (primary key) and not the text value i want - is it possible to query both so it returns say 1,option 1 - then when selected it will place a 1 in the table - which is related to option 1 in another table instead of just having to select '1' which will mean nothing to a user?
I am really new to Access 2010 as I normally use SQL so im sure this is a very basic question. I have a form where I have linked some sql tables to a table in access so the data gets stored in SQL. The form consists of many fields the basic fields are Policy ID, Name, Office, Month, Dept etc. At the moment users have to fill this in using comboboxes and text boxes but I want to change this so when the user puts in the Policy ID all the other fields are populated ie in the office field it would have a sql query of or something that looksup the office from the policy id that is entered:
SELECT [Office] FROM DBO.DQ
WHERE [Policy_ID]='what is entered into the Policy ID field' and so on.
Also not sure if this works but as I have now linked the tables to SQL does this mean that when a user fills in the information into the form it will then update the SQL table or is this another issue?
I understand right off the bat if you're reaction is "don't duplicate data!!" -- mine would be too (don't fret, I know my normalization).
I've linked a table in my db to my Global Address Book in Outlook 2007 and, upon entering an employee number as a new record, would like to verify that the number entered is listed in the GAL and then pull in the associated name and location info.
The key is that I don't want this info to rely on the GAL going forward. For example, if an employee leaves or is no longer listed in the GAL, I don't want to lose the employee info (past data is needed for audit purposes). Note: I will be creating a report later to show if there are discrepancies between the GAL and my table, but that's another story...
So, what would be the best auto-fill options in Access 2007?
I have a database with a number of linked tables that are linked to tables in different databases (not a back-end).for example, I have table1 that is linked to table1 in K:databasedb1.mdb.table2 linked to table2 in S:datadata.mdb.and so on...
However, recently we have moved all our databases to a new location.
K:databasedb1.mdb is now residing in O:masterdatabase
and S:datadata.mdb is now residing in O:masterdata
and so on...
I'm now in charge of relinking all those tables to point to the new location.I would do this in linked table manager one by one but we have 100s of tables linked to multiple different databases in different location.is there a way to create a VBA code that will automatically do this re-linking process?
so,
1. find unlinkable tables
2. search its new location under O:master
3. re-link it to the new location
Database names and tables names have not been changed. Just the location of databases.
I am trying to build a Form that will show an estimate (then eventually will be moved to a project if customer and employee aggree to price and project) in a Form F_Estimates is a M_Customers(Customer_ID) (Based on a Table) and thier info in a Subform. Also is the "projected costs" from parts out of the Parts(Part_ID) (Based on another Table) in a second Subform as a list that I need to calculate $$$ in
(Dang that still sounds evil and definately NOT understandable even after edit... so)
Here's some basic info
Tables
EstimatesandParts - Table
EstimatesandParts_ID : Autonumber
Estimate_ID : Number
Part_ID : Number
Parts - Table
Part_ID : Autonumber
PartNumber : Text (not a number due to some part#s have letters in them)
PartName : Text
Unit Price : Currency
Description : Text
Estimates - Table
Estimate_ID : Autonumber
InvoiceNumber : Text (again can have letters in it)
EstimateDate : Date/Time
EstimateTime : Date/Time
Employee_ID : Number
Customer_ID : Number
ProblemDescription : Memo
Customers - Table
Customer_ID : Autonumber
FirstName : Text
LastName : Text
CompanyName : Text
Address : Text
City : Text
Province_State : Text
Postal_ZIPCode : Text (CDN Postal codes are letter num letter...)
you can see the link table in the EstimatesandParts Table
Now I want to use that link to populate a subform in the F_Estimates form
Forms
SF_Customers - SubForm
(all boxes atm are text boxes on this form till I figure out the Parts section then will use same base for this so I can pick any customer in the database to be the customer for this estimate. Also will have ctrl button for making new customer with customer form and a refresh on Focus Gain bit of code)
FirstName
LastName
CompanyName
Address
City
Province_State
Postal_ZIPCode
SF_Parts - SubForm
Default View -Continuous Forms
(want it to be a list of parts that I can grab prices and descriptions from then in a bit of code to calculate a cost of parts)
Part_ID : Combo Box
Control Source - Part_ID
Row Source Type - Table/Query
Row Source - SELECT Parts.Part_ID, Parts.PartNumber, Parts.PartName, Parts.UnitPrice, Parts.Description FROM Parts ORDER BY Parts.Description;
(Pulls info from the table Parts for input into a list of parts to be used on that project)
PartName : Text Box
UnitPrice : Text Box
(here's where I run into problems due to the fact that the form is not based on the parts table but rather the link table EstimatesandParts so I can't propogate the info to the 2 other text boxes, ps I dont care if they cant be text boxes and have to be linked or some other type I'm not "set" just need to find out how to make it work )
(have tried a couple things to complete this task)
Me.txtPartName = Me.Part_ID.Column(2)
Me.txtUnitPrice = Me.Part_ID.Column(3)
(works AWSOME ... for ONE ROW then propogates the second selection to the first and second and third selection to first second and third and so on ...)
(tried to make control source for the txtPartName to)
=Forms!Parts!Partname
(Doesnt exist .. akkk, cant use ActiveForm either as it doesn't focus on the SubForm but the MainForm ... cry)
F_Estimates - Form
Estimate_ID
InvioceNumber
EstimateDate
EstimateTime
ProblemDescription
(all basic Text Boxes)
Employee_ID
Customer_ID
(Combo Boxes Select Customer and Employee from list of present ones of each)
SF_Customers
SF_Parts
(Both SubForms on the main form)
Now this is an Exerp from my entire Database I like to work on one small problem at a time and I have made this its own little database till I figure out the problem then I will bring the info I learn back into the rest of the database and go from there ...
Hope you can help I have a feeling I will need to make a recordset and go from there but I'm just not able to wrap my head around that for some reason
Thanks in advance for ANY and ALL help that I get from here
Hi,
I have an Access 2000 database with some linked tables. When upsizing the database I selected 'Save password and user ID with attached tables'.
Therefore in the Linked Table manager after each table in brackets I have the name of the database to which the tables are linked.
I am now wondering if I want to link the tables to a different database how do I change the database name, which is in brackets after the table name.
If I select a table and select 'prompt for a new location each time' I am getting prompted to select a different DSN. At the moment I don't have a DSN and I don't want to have to set one up.
I imagine that the database name and the user id and password I entered in the upsizing wizard are stored in some configuration box but I don't know where I can access this.
Can anyone point me in the right direction??
Thanks
I don't have any database experience whatsoever so please go easy.
I'm guessing this kind of this is extremely simple for all of you.
I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.
The first table is called "IP" and the fields are called "Address", "IP Type" and "Device".
The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".
Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)
DEVICES:
NameDescriptionAssetIP
XserveFile Server107203.30.144.75
ProliantXDHCP119203.30.144.15
IP:
AddressIP TypeDevice
203.30.144.75Static
203.30.144.15Static
What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".
I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.
thank you
-Tim
I created a form in Access that retrieves data from a table. Inside the form, I am able to access/populate data fields with data from the table. I also have data fields, inside the form, that requires key-in data. I have some how lost the ability send all data field information to a second table and clear existing data fields for new entry.
Questions: What settings, code or buttons can I use to send/store data field information to a new table? What settings, code or buttons can I use to automatically clear all data fields from my form once data has been sent to the new table?
I have main form and that has one sub-form , this Main form governs/determines the data in the sub form.
This Sub form ( DataSheet Mode) has approximately 130 columns and based on the Main form criteria only ~ 20 columns has to be filled.
What I want to do is based on the main forms criteria I want to show only the columns that are applicable to main form criteria.
If I use Columnar or Tabular single form for the Sub Form I am able to hide the fields that are not required BUT IT LEAVES A SPACE/GAP on form ( for the hidden fields that are not required)
Private Sub Form_Load()
If Forms!frmShowPIforActiveAndCanAddNewPI!FrmSubFrmFi lterProductInformationPerFMT!CASETIF = True Then
Me.CASETIF.Visible = True
Else
Me.CASETIF.Visible = False
End If
End Sub
And If I use DataSheet and hide ( visible = no) a particular filed it still shows up in Sub Form
Is there a way to Auto-Re Arrange all the fields in the sub form so that the hidden ( visible = no) fields no not leave gap
Or is there a way by VBA program to select fields ( Columns) from a table to be displayed on a sub form based on a criteria
Thanks
Rahul
I have a totalquery that runs fine and give me the sum for both fields I'm looking for but I can't get the outputs to fill the fields on the form. I have tried the Dcount query in the control source but that just returns an error and locks up access.
Code:
SELECT [Tble-wcDelays].Causedby, Sum([Tble-wcDelays].HoursDelay) AS SumOfHoursDelay
FROM [Tble-wcDelays]
GROUP BY [Tble-wcDelays].Causedby, [Tble-wcDelays].LinkingID
HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-ePlusCent]![cleanID]));
That is the query.
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.