Linked DB Query Which Allows Editing Of Data Through A Form
Dec 20, 2005
Hello,
I am a relative newby to Access, Can someone please offer a solution to my problem.
I have a DB which lists current order references, I also have a linked spreadsheet from a customer which references their Ref number to our S/order no & Item no.
I have set up the relationships for the common data between both tables.
if I create a Query from either table individually the data can be edited but when a query is created from both sources the data cannot be edited.
this also happens if the spreadsheet is imported to its own DB.
I would really like to create a form which displays both sets of data for editing, marking progress etc.
Can data from linked tables be edited in a form ?
Regards
Andy
View Replies
ADVERTISEMENT
Mar 28, 2008
Hi all. I have an excel spreadsheet that pulls company information off of our internal system that is linked to an access database table (let's call it "General"). The General information is current and is updated within our internal system (but through the Access side will not need to be updated), however, I have a separate table (let's call it "Detail") that has information (employment, revenue numbers) that I want users to input through access. The two tables are joined through a relationship, however, b/c General is a linked table I am a) unable to assign a primary key and b) unable to edit the Detail information in a form view. I know this is related to Microsoft's limitation on excel linked table, but I was wondering if there is a way around it besides copying the General data into an access table. Would be grateful for any help!
View 2 Replies
View Related
Feb 18, 2015
My aim is to have my forms open to a new record, which I have done, but if my users need to then update or edit data in previous entries, they can click a button that allows this.
My thoughts were to add a button, then put in code so that the necessary properties changed the form from displaying a new record or records entered since opening it, to showing all records in the associated table....
As an example there is a table for purchase orders. When clicking on this from the main menu form, it opens up the purchase order form to create a brand new PO. At times though, we will need to revisit an order to attach a copy of an invoice, or update the cost of whatever was purchased.
View 5 Replies
View Related
Jun 8, 2007
Hi Guys
I'm trying to create a database application which logs the number of times which i do a certain task.
Is it possible for me to create a button within a form that will increment the value with a particular field by one?
I have searched the net but can't find anything.
Thanks
Matt
View 6 Replies
View Related
Jun 20, 2005
I have a form to edit records. When I open the form I am not able to change any of the fields until I edit a date field that has a calendar popup on the "on click" event. After that all of the fields are available for editing. Any ideas??
Thanks,
Pat
View 11 Replies
View Related
Jun 12, 2007
Hi All
I'm trying to set up a form to log my clinical placement, and the number of time i undertake a procedure.
I want to have a list of a few procedure(fields) and then button next to each one in a form which will increment the number by one
How do i go about doing so?
View 3 Replies
View Related
Mar 24, 2014
I have a form with several subforms within (tabbed subforms). I've used the code:
In the Current event of the parent form -
Code:
Me.AllowEdits = False
Me.AllowDeletions = False
Me.AllowAdditions = False
In the Current event of each of the subforms -
Code: (THIS NEVER WORKS FOR ME - RUNTIME ERROR)
Me.AllowEdits = Me.Parent.AllowEdits
Me.AllowDeletions = Me.Parent.AllowDeletions
Me.AllowAdditions = Me.Parent.AllowAdditions
In the Click event of the button -
Code:
Me.AllowEdits = True
Me.AllowDeletions = True
Me.AllowAdditions = True
So the main form is locked upon opening and unlocked with the click of an unlock button. How to apply this to the subforms as well. They just stayed unlocked the whole time.
View 14 Replies
View Related
May 1, 2013
I have produced a table, the records from which have to be selected by individuals for auditing purposes.
I created a second table with individuals initials and passwords. This is linked by the initials to the main table. Records on the main table are returned when the query is run.
At first I could not edit fields in the query but changed the recordset type to Dynaset (inconsistent). I am now able to edit the query fields. However, having created a form based on the query, I am unable to edit the fields in the form. This is the basis that the auditors will use to score so is vital. Have I missed any other switches or techniques?
View 6 Replies
View Related
Jan 19, 2007
I am trying to generate a query from a series of linked tables in a "linear" arrangement. My central table is an employee table. I then have two branches for phone data and office data, each in a many to many relationship with the employee table, so I have a linking table between.
Phone<-->Employee_Phone<-->Employee<-->Employee_Office<-->Office
I am trying to generate a directory that lists for each employee their phone number and their office number. Whenever I try to generate my queries however, I get each phone record for each office record. If I have 4 phones and 2 offices for an employee, I then get 8 rows in the query. Is my table setup just awful (my attempt at normalizing my data as many offices and phones are shared) or is there a clever way to generate a query I haven't yet discovered. Any help would be great!!! (lest I start )
View 10 Replies
View Related
Mar 26, 2014
I have a file "MatchingData" thats linked to another file "Clist" . What I'm looking for to open "MatchingData" and update from "Clist" on opening. I don't know if I can do this without opening "CList". I have a feeling that I may have to open the "CList" (Hidden), update,then close "CList".
View 2 Replies
View Related
Jun 28, 2013
I have a form that I call the AdminForm. It allows the user to store administrative information about the business; business name, address, telephone number, etc. I also have as a part of the table that stores this information an Attachment Field where an image of their logo is stored in a field called, [Logo]. No problem here. The issue is that I want to use the [Logo] field elsewhere like in reports.
I've added an Attachment type control to the report and I've used DLookUp to use the image stored in the admin table, but it doesn't display on the report. There isn't any relationship between the admin table and any report so I can't join them unless I do something "unnatural" by adding a fictitious field to every record to tie it back to the key field in the admin table, (there is never more than one record in the admin table; I ensure that). That doesn't seem like the right way to accomplish this.
View 9 Replies
View Related
Jul 30, 2012
I'm using Access and Excel 2007.... I know how to import an Excel spreadsheet as a table.
I have several supplier price lists in Excel. I want to keep my vendor price lists up to date.
When one of my vendors tell me that a price has changed on a particular item, I figure that I could have a form that I could use to enter the changes.
I believe the form would look like:
Field: "Vendor" (drop down list to choose from. Name of the Supplier price lists) Required.
Field: "OEM" (Key Field found in each table) Required.
Field: "Brand" (Field found in each table) Not required.
Field: "Price" (Field found in each table) Required.
OEM would be the unique key field.
If I enter the Vendor name and then the OEM number it would show if there is already that number in the Vendor price list and I could make changes. Or I could enter new data in that vendor price list.
View 10 Replies
View Related
Oct 19, 2006
I want to make a field where data can be enterred but once its saved cannot be deleted or changed in any way.
Anyone have any ideas how this can be done?:confused:
View 10 Replies
View Related
Mar 8, 2014
i need to Concatenate a fields data into one cell in a query according to linked table ID....
View 3 Replies
View Related
Oct 4, 2007
Hi,I have a table which contains sales information like deal name,closing date of deal,Monthly revenue information,total revenue yearly also quarterwise
information.The below table gives some idea....
i want to enter data in multiple fileds ex:when I enter revenue amount it should get updated in corresponding month based on the closing date information.Suppose closing date for a particular deal is mar-08 then the total revenue entered in revenue field should get updated in march month field.Please help me how to edit data in multiple field and also logic to apply to solve my problem.I have to design a report based on this information and source should be one table so i want to edit data in main table.
Any help would be appreciated...
I am new to ms access database so got stuck..pls help me
Closing date Jan Feb ar Apr May Jun Revenue Q1 Q2 Q3 Q4
Mar-08 1,500 1,500 375 0 0 0
Jun-08 4,000 4,000 0 0 0 0
Jun-08 6,000 6,000 0 4,500 0 0
Jun-08 3,000 3,000 0 0 0 0
Jun-08 1,720 1,720 0 0 0 0
Jun-08 20,000 20,000 0 0 0 0
Jun-08 2,000 2,000 0 0 0 0
Jun-08 20,000 20,000 0 0 0 0
Thanks in advance. yuor help is appreciated,
Regards,
kala
View 2 Replies
View Related
Jul 11, 2013
In my form I have a subform, which displays A, B, C. Then information C is displayed in textbox. User should have a possibility to modify this data in order to modify data in database.
I am able to display information C in text box based on this subform. However it is uneditable, although property of enabled is yes and locked is no.
It seems to me, that it is impossible to edit data because it is taken not directly from database but from sub form, so I make special query which takes data directly from database however I lose an event which is based on selecting proper record from subform (there is only onEnter and onExit events)
View 5 Replies
View Related
Apr 12, 2013
Is it possible to have a 2 step confirmation process.
I have a yes/no control box to confirm that the record is OK or correct.
Is it possible to have a second process where by I can lock the record from erroneous data entry or editing? Like a "Post" in accounting.
View 3 Replies
View Related
May 4, 2006
I've been looking around and have found some posts that pointed me in the right direction, I just can't get it to work. What I have is 37 excel files of competitor cross references. There are 2 columns in each excel file, our number and their number. I have inserted them as a linked table in the db. What I want to do is create a form that has a field for every part number and make all of those fields a search field. That way they can type in any number and get all numbers back. I have created a query but once I get past 4 linked excel sheets then I get errors about a type mismatch in expression. Also I can't get the form fields referencing the query to show up when I open it. If I leave the query at 2 or 3 fields and use [Forms]![CrossRef]![txtItemNumber] in the criteria of our number in the query, it works. I get a window that pops up when I just open the query asking for a number, I type it in and the query returns the number and competitor numbers. Am I doing this the hard way?? Thanks for any ideas...
View 1 Replies
View Related
Apr 14, 2005
Please can you help me find the issue to my problem that is probably hitting me in the face.
I have an unbound form to which i use an append query to post the data from the fields on the form to a table.
The table is set up very simple and has a primary key which i will never duplicate as you would expect.
Once i have inputted one lot of data into the form the command button runs the query and the data is posted to the table fine. The problem i am having is that the second lot of information i go to append the query refuses to add.
I get the validation rule violations error and it won't post the data.
If i close out of the form, open it again and input the same information the query adds it fine and again the second lot you get the error message.
i had this problem before but for the love of me cannot remember how i resolved it. I have had to keep closing the form after each entry to post the information.
Any help much appreciated.
View 2 Replies
View Related
Sep 29, 2014
I am using Office 2007 and have a form that is linked to a query, also I have a sub form linked to the same query. I cant understand why both forms say in their properties that they are linked to a query but when I try and add a text box to show customer ID it tries to show data from a table
View 1 Replies
View Related
Mar 27, 2015
I have a form and I want show a message on the form when the it is locked as another user is editing the data in a particular record.
I know the record selectors show the records lock status but it a very tiny symbol which will mean nothing to the users of the database and anyway I don't want record selector bar on the form. How I would do this???
View 2 Replies
View Related
Oct 14, 2013
I am making a booking system where a user enters
StartDate, EndDate (Form Header)
House , Room , UserID (Form Footer)
The Header and footer are not linked. The Footer simply displays all the existing bookings for said ouse/Room/Date combination. (Date being all dates between the StartDate and EndDate) BUT (surprise, surprise)users don't look at this to check if a booking already exists.
Also - It seems pointless to have the users enter the same data (House, Room, UserID) in 6 times (one for each day that they want to book the room.
So I am trying to automate the process.
Therefore, I append each 'new' record (that the users adds in this session) into a temporary table. (House / Room / UserID / StartDate)....note, no end date because I need a separate record for each day.
I have a query that is supposed to check the EXISTING bookings with the temp table.
My code (paraphrased here for simplicty) says
do while StartDate < EndDate
- if Qry_CheckForClash returns 0 records then '(uses a dlookup)
- append from TEMP to BOOKINGS.
- - else
- compile an alert message to the user (appending the Room No and Date each iteration)
- In Qry_CheckForClash, increase the StartDate by +1
- end if
loop
My problem is that after the last step (increase StartDate by +1) the call to Qry_CheckForClash still returns the initial StartDate. But when I check the TEMP table, the StartDate has changed.
So how do I REFRESH or REQUERY a query that is not associated to a form or report?
View 8 Replies
View Related
Mar 25, 2015
I have a form that is linked to a query by its Record Source.In one of the Text boxes I want to appear the number of rows in the query where Days = 4 so I thought I would try to use DCOUNT as the Control Source for the Text Box. However every expression I try for DCOUNT throws back error? or name?. How to sort my code?
=DCount("[EPISODEUNIQUEKEY]","1-12 Show Records by Date Range","[Days] = '4'")
View 3 Replies
View Related
Mar 24, 2006
I set my form in Design view to Allow Edits but not Additions, and when I switch it to (or open it in) Form view, all the combo and text boxes and buttons disappear (the background color and header are all that's left).
All I want to do is edit existing records through the combo boxes. How can I correct this?
View 4 Replies
View Related
Mar 20, 2007
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 "*"
View 2 Replies
View Related
May 22, 2006
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
View 7 Replies
View Related
