I have a form with a text box from a table (cus. ID) and when I input that cus.ID into that text box I would like for that data to be stored in the appropriate table (customer) and also in the bridge table (customer purchases). Is there any way to input the data once into a textbox on the form and have it recorded into two different tables? Please let me know. Thanks
View RepliesThis is very bad. We're losing data changes when we run a synchronize on an Access database.
A client wanted to use replication so he could have several people using the same database and then update everyone's changes. (It turns out that he's not used that feature.)
The client was unable to get the replica to work on his machine, so he uses the master version.
Instead, we've used replication to allow me to make changes in the database structure while he makes changes in the data. (Obviously, it would have been better to split the database in two parts, but it's too late for that now.)
Here's my process when I need to update his copy:
1. Synchronize between my master and replica.
2. Copy master to CD-R. (We're not on a network.)
3. Synchronize between client's master and replica.
4. Send a copy of master to client.
5. Open my new version of master on the client's computer and synchronize with his replica copy.
When we do that many of his recent changes disappear.
Why is that? They don't even show up as conflicts.
What are we doing wrong? Are we totally abusing the concept of replication or what??
I'm stumped. I appreciate any suggestions or thoughts.
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 a command button that I want to automatically enter the date into a date field on the same form.
Setvalue
item: [Forms]![FRM_View_Orders]![OrderDate]
expression: Date()
This works in Access 2000 -- but in Access 2003 I get this error message:
"Object or class does not support the set of events"
Please can anyone help me with what I am doing wrong in 2003.
Thanks!
Hi Guys,
Haven't done anything with Access since I finished uni and I can't remember much. I'm trying to set up a simple database with customer recording and order taking functionality. Just in the very early stages trying to get basic things working.
I have a form where you enter customer details..at the bottom there is a command button named "Place Order", which will be clicked to go to the order form for that customer. Attachement shows very simplified version of the order form. I need to enter a Product ID, and have the Product Name automatically extracted from the Products table.
How can I do this? I would have done a search...but not even sure what this process is called in Access.
Thanks for any help.
Having problems getting dlookup to work in the control source field of a text box.
My form has fields : Catalog # (numeric value) and Country (drop down text selection).
I would like to query a table CatNameList for a name (text) if the catalog # and country find a match on the table.
My field names on the CatNameList table are : Name, Number (to validate against the Catalog # entered on the form) and CName (to validate against the Country drop down on the form).
I am successfully able to populate the name from the CatNameList table on my form using lookup of the catalog # using this :
=DLookUp("Name","CatNameList","Number = Form![Catalog #]")
However, I will eventually have several catalog numbers that will be identical in the table CatNameList, thus why the country is important as the second criteria to be added into the dlookup.
I have tried for a few hours unsuccessfully to add the second portion to my dlookup.
This is what I have currently (not working) that I have been playing with, I'm sure I'm missing a quote mark, & or something simple.
=DLookUp("Name", "CatNameList", "Number = Form![Catalog #] And CName = ‘”& Form![Country] & ”’”)
I have a database that will register the emails coming in and what time, also the time, date out and person.
I have a form with the fields to be filled in and a submit button. There are some fields that are automatically filled in and others need manually fill in.
below that part is a sheet (subform in the form of sheet) that should be filled in with the above data. Once it is there, it should empty the fields so they are ready to be filled in again. If I fill new data and press the submit button, it should go to the next available row.
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.
When I synchronize my replicas not all the data is being updated. What would cause this and how can I fix this?
View 2 Replies View RelatedAnyway, I would call myself intermediate level at best with Access. I never expected to have to do so much with it, but when my bosses found out I could do Access basics, they began demanding more and more.
I manage an EMR from which a datapull occurs on discharge to various access databases.
They wanted me to add a triage patient data pull to track what procedures are being done to triage patients. So I built a database with the following 2 tables.
tblTriageVisits and tblTriageProcedures
tblTriageVisits has the following fields
-Patient ID
-Export Time
[Code].....
The tblTriageVisits stores all the patients triage visits. The other table stores what was done to each patient on those visits
It should also be noted that this EMR exports times in number of seconds since 12/31/1975, so TimeProcFMS is the number of seconds since that date for example.
The pulls work great and the duplicate record elimination method works great.
I have to design a couple of different reports based on this data and one in particular has pretty much flumoxxed me.
I need to make a report that lists each patient by date they were there and then shows what was done to them. I have tried several different variations of this as both queries and reports. The report breaks down when I try to get it to show just those procedures that would be for that triage visit. I got to the point where it would group by day and then sort the patients alphabetically. Then I added in the visit fields. And it would put all triage procedures for that patient from all their visits in each visit.
So, I was like great, let me filter out those that don't occur between the two dates, admission and discharge
I used the following two formulas (the first one on the procedure name field the second on the procedure date field)
=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],[Procedures FMS])
=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],DateAdd("s",[TimeProcFMS],#12/31/1975#))
And they work, sort of. They do eliminate those procedures that occur outside of the date range I am looking for. However, the access report still leave spaces in the report where the filtered out items would be.
Did I approach this problem correctly? And if so, how do I get rid of those spaces?
Note, I can read and mostly understand SQL, but am not comfortable working in it yet. And the Access SQL box gives me a headache, it is so jumbled up.
hello. can anyone help me with replica conflicts?
i need to work with 10 replicas and synchronize with a master, but anyone can make changes and modify the records on the other replicas. is there a solution for that? why access doesn't recognize the changes as a conflict?
Thank you all.
Hi,
I'm a student learning how to use Access for my class project (Information Technology in a Global Society), and I chose to work with databases (even though, frankly, I'm technologically challenged). I've already finished making a very elementary database (tables, form, and a macro or two), but I was wondering if there's any possible way to synchronize the material in each table in such a way that changing the information in one would automatically update information in the other.
(I have one main table, and several other sub-ones with the exact same information but only parts of the main table. By this, I mean I created a main table with every student's Extended Essay topics, along with their supervisor as another field. Each supervisor oversees an essay for several students, so I created sub tables for each supervisor with the information of only their students.) What I'd like to know is if I can keep one main table with every supervisor's information and separate tables for each supervisor -- while, at the same time, having the main table update with the information that's added to the sub-tables by the supervisors.
I considered using Relationships, but after going through the threads here concerning relationship issues...I got even more confused.
Any help would be greatly appreciated.
<3, Patti
Hi, Could anyone please help me!
I am trying to create a sub-form which achieves the following.
When you select a vendor from a combo-box, only the software that is manufactured by that vendor appears in a data-sheet form view.
I have tried to create a secondary form which shows all the software and saved this. And then created another main form which has a combo-box for listing and selecting a software vendor. I have then dragged the secondary form into an area of the main form. Now i am just trying to figure out how to synchronize the two forms so that when you select a vendor, only software manufactured by the vendor is shown.
How do I make the name appearing in my combo box to change to match the name displayed when the record navigation buttons are evoked. Currently, when I use the navigation button, the form scrolls to the next record, but the name in the combo box does not change to match the new record?
tx!!!
I have a database that is replicated and in my attempt to synchronize a message keeps popping up "Synchronization between replicas..... and ....... failed. The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
The problem is I don't know what field in what form or in which replica this is referring to. Please help!!!!
We have multiple users using the same database as local copies on each of their PC's. they are entering information in specifically assigned areas and no one is overlapping in their work areas. The users then synchronize to a master database that is on the network to transfer their info.
When synchronizing we are beginning to conflict errors, and the message is hard to decipher. Below is an example:
"Update/Delete conflict: Another replica also updated this record. This record lost the conflict. Either resubmit your update or delete the conflict record."
It then shows two columns. Column 1 has the option to "Keep exisiting data" an dcolumn two has the option to "Override with conflicting data".
Technically, when synchronization happens, data of the user who is synchronizing overrides existing data in the master database in the same record(s). In that spirit, what is the 'existing' and 'conflicting' data referred to in the message?
Hi there :)
I'm not sure if I've picked the right forum topic for this... It's not a straightforward table question, and I think the only way I can do what I need is with a query of some sort. Apologies if it should have gone in 'Tables'...
The situation is: I'm creating a database (using Access 2003) in which our secretaries can enter exam marks for students. The difficulty is that there are ten papers (exams), each student's exam is marked by at least two examiners and there are at least 6 examiners per paper. Every examiner also marks more than one paper, but doesn't mark all papers.
So, I've got a table with all of the examiners and the papers they're marking and I've got a table with all the students and the papers they're taking. Because we have to enter the marks in such a way that we know both which mark an examiner gave and (obviously) which mark a student received (e.g. so we can look at statistics for each examiner), I thought the best way to do this would be to run a query that created a new table per paper in which the field names would be: Student_ID, <name of examiner 1 for the paper>, <name of examiner 2 for the paper>, <name of examiner 3 for the paper>, etc until the end of the list of examiners for that paper. The student's number would populate the Student_ID field and then the secretaries would enter each examiner's mark in their named field. Like this:
| Student_ID | A.N. Other | J. Blogs | J.Q. Public | Agreed_Mark |
1234A 72 74 73
2345B 65 68 67
3456C 71 73 72
4567D 52 51 52
Listing the name of every examiner (including those who aren't marking that particular paper) isn't an option (and would have to be hard coded into a table in any case, as far as I'm aware, either in a query or in a table design. This is something I'm loathe to do). Ideally, the secretary would be able to press a button on a form and have all the mark entry tables for all the papers created automagically.
The examiners for the different papers change every year, and not every academic marks exams every year, so this data will not remain static. Hard coding a table with examiner names would make more work for the secretaries. (I want to avoid this as all mark entering for all students is done in a single day. This means over 600 students at 3 marks per student [mark 1, mark 2, and final agreed mark] with time to contact examiners with questions... and only 2 secretaries, each of whom deals with a different section of students).
Because of the nature of the marking, it would also make quite a lot of work for the secretaries if we had to cross reference a generic Examiner_1 field with a different examiner for each paper, so the field name really must be the examiner's actual name.
Can anyone help me? I'm happy to do it with either VB or SQL; I'm not a programmer, but I can usually work out what code does even if I can't write it myself. I should be able to adapt anything presented as long as it's complete and doesn't assume that I know the basics. :) I've also got two reference books to hand: _Microsoft Access 2003 Forms, Reports and Queries_ by Paul McFedries and _How to Do Everything with Microsoft Office Access 2003_ by Virginia Anderson.
Thanks very much for your time. :)
-Jen
Hi There,
I want to synchronize 2 comboboxes (actually 3, but let's start with 2)
On a form I have 2 comboboxes:
- CompanyType (FK fkCompanyTypeID in table Contacts)
- Category (FK fkCompanyCategoryID in table Contacts)
Let's say we have companytype CT1 until CT10.
For CT1, CT2 & CT3 I want to choose a catagory, so the combobox must be 'enabled'.
For CT4 until CT10 the Catagory combobox must be disabled, so that I cannot choose a category value.
Actually I want to choose 2 catgories (2 comboboxes) for CT1 until CT3.
But I think that an explanation for the example above is sufficient for me.
Both comboboxes are filled by a Select Query, and the values are filled in a separate table. (Table CompanyType (PK pkCompanyTypeID) & Table CompanyCategory (PK pkCompanyCategoryID)).
Can someone please help me on this issue? :confused:
Thanks in advance for your reply,
Greets,
Quinten
In Access 2010, in a table or form, are you able to use data from a lookup table like you can in a vlookup in Excel?
I am creating a Student database starting with the MS Students template. I will have a Parameter Table that contains weighting factors for each test for a given class. When scores for a particular test are entered for each student in a Test Results table, I would like to use the weighting factors for that test from the Parameter Table. These would be used in formulas/expressions to calculate a weighted score for the particular test as well as class-to-date average for all the tests to that point.
It seems this can be done in a query, but I am wondering if it can also be done in form so that when the instructor inputs the test scores the calculations are performed and the results of the calculations displayed. Or, if not, can the query be "built in" to the form to accomplish the same effect?
I only have one table and I read so often that its better to have multiple tables. For what I have, it seems to work fine.
Will this cause problems down the road?
Thank you once again
John D
Hello
I have a table with 3 fields:
- ID no (primany key), Time1 and Time2
I have 3 records with ID no 1 to 3.
So my table look like this
ID no: Time1: Time2:
1 [emty] [emty]
2 [emty] [emty]
3 [emty] [emty]
I have an Excel document with the data for field Time1 and another Excel dokument with the data for field Time2. Excel doc1 have 2 colums named ID no and Time1. And Excel doc2 have 2 colums named ID no and Time2.
How do I import/update these data into my table?
I have tried wiht a adding-quiry but it only works if the table is complety emty and only with one Excel doc. Trying the second Excel doc afterwards only makes an error and no fields are updated.:confused:
I am trying to populate fields from one table into another field in a linked table. Specifically, I have a giving table and a persons table. I have the giving table have the persons primary ID field in the giving table. I cannot seem to automatically link them if I am adding a "gift" record. The gift is a dollar amount given by that person. I have a unique primary key for each financial contribution which is supposed to be tied to a person's primary key and last name in my "person" table. Am I missing something in this design?
View 3 Replies View RelatedHi 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 database with a number of tables 2 of which are:
# Personal Members Table (with all their details and addresses and contact details)
# Organisation Table (including who are members of this Org)
My conundrum is this; In the Org table I have just created a 'Treasurer' Field and want to choose a name from the Members Table so that I do not have to put the data in twice (which is what has happened in the past.)
I have linked the tables and but within the Members table the Field names for the Personal Name are as follows <FirstName> <LastName> and there is another for the <Org> they are connected with...
In the Members table it would also be good to have the ability to hover the mouse over the <org> field (or click) on it and then underneath a box appears with everyone who is a member of that org. I have seen this working on another database but cannot seem to replicate it here for some reason?
I have an ... interesting issue. I am wondering if others have dealt with this. I have a solution, but am not the happiest with it.
I basically have 2 databases here. (Or I will when I'm done making them) and one is dependent upon the other. In fact, the 2nd's tables are dependent upon the fields in the first database's table.
This database is for quality control checking purposes.
First off,
Database 1:
Basically a list of standardized audits and what needs to be checked for those audits. What needs to be checked can change as things progress, but stays pretty standard.
Database 2:
This will hold the checks that the QC branch will do, based on what type of Quality Check they are doing, they have an audit standard. This audit standard exists in database 1.
What basically ends up happening is that the data in Database1 become Table fields in database2.
Example:
Database1:
Radio Check Audit Standard.
Needs to check following:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.
Another Random Check Audit Standard
Needs to check the following
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Did something specific for this Audit
Didn't do something else, also specific for this Audit.
Etc.
Continue on with other Audits.
Database2: Will keep track of all the quality checks that have been preformed.
Today we will check Radio Protocol
Did they:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.
Yes, No as necessary.
2 Days later, we need to do another QC on the Radio Protocol
Need to check if they did:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.
Etc.
But what will be checked for in Database2 depends on what the standard is for the "Radio Check Protocol," or etc.
Right now, i see 2 options:
Option1
Make the 2 databases, and when a user "Picks" the Radio Check protocol, a linked table is filled with the Name / object that needs to be checked with a field in the same row to hold the data if the object was done / not done etc.
The fields in Database1 are automatically put into this specific instance of Quality Check, etc.
Only problem with this: Requires coding to auto update. Easy to do, but if errors occur, things will screw up.
Option2:
When the user picks the Radio Check protocol, simply allow a subform with a refreshed drop down box. Allow user to pick items they need (Limit it to only this Audit list, etc) to set to true. Assume false for other items not specifically chosen by user.
Problem with this: If an Audit standard changes to remove an item, or add item. Any quality checks done will also change for the assumed false items. If another Item added in, then all old QCs will now also have that item added in / reported assumed false. And if it is deleted, all old QCs will loose the item that was reported false.
I'm seen the first option as the only viable one.
Am I missing this, is there some other way to do this that would be better / wiser? Etc?
Kelemit
Hello,
Been wondering how I can update fields in my Access database table using data that lies in an excel spreadsheet.
They have a common row ie say account number and other common fields that need to be updated.
thanks