Capturing Updates In Tables - Part 2
Nov 24, 2007
Could you answer a diabolical question? I am trying to track the changes of a record in a table when I have imported a text file.
I will import a txt file everyday and to that end I have wrote the code for the insert of new records into customer table and customer history tables and then the updates into the customer table.
However I will only be able to capture the updates in tables as there will be multiples. I understand that Jet can't capture the updates at the table level (no triggers).
So the diabolical question, is it possible to capture the updates only in tables using code??
Please help...
Many thanks
View Replies
ADVERTISEMENT
Jun 17, 2013
I've been tasked with creating a database for responses to a survey (that's already been created). There are several aspects most of which i've been able to handle (i.e. the Demographic portion)...The survey has 12 questions with pre-defined answers...What is the best way to set up the "question/answer" side of the survey.
View 1 Replies
View Related
Feb 21, 2007
I am trying to come up with a way to update tables every week from two databases. I have one database for a satellite office and one for corporate. Each office has BOTH databases so as to keep up with their own data (we are not on a network). Each office will have to export their Project Table data to the other and then upload (update) the corresponding database. So:
ABC Division exports their project data from their Project Table once a week and sends to DEF Division and vice versa. Then ABC takes the exported Data from DEF and updates their database for DEF and vice versa.
Field names and additional field names will not happen; however, the data in those fields may be updated OR there may be new records added. So basically, I guess I need to delete the old and put in the new every week but make it VERY easy for the end users (who know nothing about Access). The tables that would be updated are related to other tables.
Does anyone have any suggestions where to get started? I've tried an Append query and that only adds records, not looks for changes in those records.
View 8 Replies
View Related
May 17, 2005
is there anyway you can create one form which updates all the information in multiple tables. I need to create one of these to make the navigation of the database more easy, so if anybody could help me it would be much appreciated.
thanks.
View 1 Replies
View Related
Jul 10, 2012
I have built a database and within it my queries I use for reporting make tables in a separate database. This allows me to work in my data base and update info as needed. My problem is that my make table queries wont run if anyone has one of the report pivot tables open. I have added macros to all the Excel files that pull from the reporting DB but I still have several times when I cannot update because someone is using the pivot table, Is there a way to allow me to update the report DB's while the pivot tables are open? I have tried using manual refresh methods and disabling auto update on open but the result is the same.
View 2 Replies
View Related
Dec 16, 2004
I have what I think is a difficult problem to overcome...
I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.
Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!
If this is not possible, do you know how I can acheive this?
Thanks
S
View 1 Replies
View Related
Aug 2, 2007
I am pritty new to access and have been tasked with creating a database !
I have two tables one called main which has 26,000 claims (listed under there part number).
The other table called part numbers is a list of all the part numbers in the main table but with no duplicates (126 part numbers)
I need to somehow count how many times a part number apears in the main table. Maybe display a count in the part number table as a extra field ? then i could put this field into the form i created to browse the parts numbers (to show how many times the part number appears in the main table)
View 12 Replies
View Related
Apr 26, 2005
Hello everyone. I'm pretty new to access and need some advice on table structure for a new project I've been given.
One of our customers sends us an Excel spreadsheet each week containing their order. Currently, someone formats the spreadsheet, prints it out, and manually picks out the products we need to ship. I want to import this into an Access table. Basically, the sheet with some data examples looks something like this:
CONTRACT_NUMBER----PRICE----COMPONENT----LOCATION----SHIP_DATE
----------------------------------------------------------------
11111111-----------393.67---AAA1000------290---------4/29/2005
22222222-----------415.00---ABB2000------310---------5/1/2005
There are usually 30-40 records on this list. The COMPONENT field is actually a part kit. We have tables in a Excel sheet that list the parts in the kit as well as the quantity. For example.
Component: AAA1000
will contain
Part#---------Quantity
----------------------
123-----------2
456-----------17
789-----------11
111-----------57
908-----------36
--------------------------------------
Component: ABB2000
will contain
Part#---------Quantity
----------------------
123-----------80
777-----------29
345-----------3
906-----------14
What I basically want to do is run a report based on a query where each page has a contract number (one for each record) with matching ship date and location information and a list of parts needed on that kit.
For example, the first page would be:
--------------------------------------------
Contract #:---11111111
Location:-----310
Ship Date:----5/1/2005
Kit-----------AAA1000
Part#---------Quantity
----------------------
123-----------2
456-----------17
789-----------11
111-----------57
908-----------36
--------------------------------------------
and the second page would be:
--------------------------------------------
Contract #:---22222222
Location:-----290
Ship Date:----4/29/2005
Kit-----------ABB2000
Part#---------Quantity
----------------------
123-----------80
777-----------29
345-----------3
906-----------14
--------------------------------------------
I'm a little confused by the COMPONENT field linking to more than one part. In the little experience I've had with Access, one record was always linked to just one other record in another table. Also, I want the Contract Number, Location, Ship Date, and Kit # show up once at the top and the complete kit show up below.
I would appreciate any suggestions as to hot to set up my tables. We have about 30 different kits. Should I have a separate table for each or a huge table? Also how will I make relationships between the main table and the part table/tables? Am I going to have to have the kit # as a field in the part table and list it for every part/quantity record? I want to just list the description information (location, ship date, etc...) only once while listing all the parts and quantities for the kit.
I've only been using Access for a short time, so this might be a really simple question. I just need to be pointed in the right direction, so I can set the system up the right way. Any information would be greatly appreciated.
Thank You,
Chad
View 1 Replies
View Related
Jun 25, 2013
I have just inherited a database that currently has no primary key set up. I wanted to add a two part primary key, but have run into a couple problems. The purpose of the database is to keep track of parts that have come back for repair. I wanted to set the key up to be a combination of the call number and the part serial number. I should always have a serial number, but the serial number can be in the table more than once, as it could have needed repaired more than once. I will normally have a call number for each record.
However, the call number could be repeated more than once, if more than one part came in on the call. The combination between call number and serial number should always be unique (each time a part comes in, it should be on a different call). My problem is arising, when a part is just pulled off the shelf of the factory and sent in for an upgrade. In a case like that, there is no call number. However, a serial number will never come in more than once without a call number.
So the combination of call number and serial number should still remain unique. When I try to set this up in the database, it tells me that the primary key cannot contain a null value (referring to the times that a serial number comes in without a call number). I want to get this set up so that the people entering data in the database will not be able to enter the call number, serial number combination more than once. How can I do this if one part of the primary key can be null?
View 5 Replies
View Related
Apr 6, 2015
I have two tables (A) and (B).
Table (A) contains key-field name "position1".
table (B) contains key-filed name "position2".
How to make a link between key-field "position1" and part of key-field "position2".
View 5 Replies
View Related
Oct 17, 2014
My table key looks like this. Its a table that keeps a master record of conferences that occur during a calendar year
ConfYear (Date/Time) primary key
ConfNo (Byte) primary key
As you see its a composite key. Now each time a new record is created i want the ConfNo to autoincrement within its ConfYear. I used a byte as its not usually more than 4. Autonumber does not work as part of a composite key i found out.
2014 1
2014 2
2014 3 etc
And revert to 1 when ConfYear input changes to 2015
Is there a simple way to do this with say, a macro, or is the only way with VBA and DAO?
View 12 Replies
View Related
Feb 26, 2015
I am creating a database that tracks the selling of products amongst other things.
The user will enter in an order and may delay invoicing until the customer approves the quote - at which time the order is turned into an invoice.
In the transactions table the OrderID, ProductID and CustomerID constitute a composite key.
I want to be able to view the order and change it by either deleting or adding ordered items (obviously prior to invoicing) but because the ProductID is part of the composite key I cannot delete a line item.
Perhaps the solution is to remove ProductID from the index or is there a better way?
View 8 Replies
View Related
Nov 3, 2006
Hi everyone,
I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) )
However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())
Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.
Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.
Many thanks,
View 6 Replies
View Related
Nov 14, 2012
I have a master list of projects, with project reference number, project name, and nature of project.
I have also got a form for individuals to fill in details of project events, with date, time, name, and two or three other fields - also included are project reference and name. I'd like the name field to be auto filled when the user selects the project reference from a combo box; I think? (the list only shows open projects).
I'd did something similar some years ago in Access 2003 (I think) but cannot figure it out in the version I'm currently using 2010.
View 8 Replies
View Related
Sep 18, 2007
Hi,
I've read a few posts regarding removing the facility of the F11, open Db window, but is there a way to capture if someone presses F11?
I have a few people (users) who think they know a thing or two about Access and I'd like to know if/ when they're accessing the Db window.
I was hoping for the ASCI code or similar for the F11 key!?
Thanks
Matt
View 4 Replies
View Related
Jul 7, 2006
I have a form that records when a user edits a record. This change is only recorded when the user clicks the "update record/save" command button. What I need help with is capturing the changes made when a user edits the record in any field and doesn't click the save button upon closing the form. If the user doesn't edit any fields in the record, a new instance of this edit will not be saved to my edited records table and the form just closes
View 2 Replies
View Related
Aug 1, 2006
Hi All,
I'm a novice to Access and I need some help developing a form page where by users can input and record daily project activities.
I have most of the form complete, however, i find that when a user accesses the page the previous record is sometimes showing. How do I setup my form page so that all fields are blank everytime? And how do I set it up so that a user can't scroll through records in the form screen.?
View 2 Replies
View Related
May 16, 2006
Hello all.
We have product orders that get held in process for a variety of reasons (contract not signed properly, missing drawings, etc). It is our Order Processing Rep's (OPR) responsibility to review the requirements, follow up with the salesman, get the proper documents together and get the order released for product assembly. We capture the date the order was originally held with a date-formatted text field, and then we later capture the release date with a similar field, entered by the OPR.
The OPR is graded on the average time it takes to release a held order. Since they can enter text into the release date field, they can fudge their metric. I'd like to fix this by having a button on the form say "Order Released" and, when it is selected and verified by a second click, the date that occurs is captured in the table under "Release Date" but is hidden from the OPR's ability to edit it.
Can this be done? Any help is greatly appreciated!!
Tom
View 5 Replies
View Related
Jul 29, 2006
I have a report which asks for the beginning date and ending date which they want. The report is generated using this information. I want to put the information entered in the parameter request into the heading of the report. How do I recover this information to put it in.
Jerry Hughes
View 2 Replies
View Related
Mar 22, 2015
I am sure I am not asking a question that has not been asked before. I have a form and i can capture who edited the form using log in ID. But now the issue that is coming my way is that certain date field are more critical and we need to know who amended those dates and whether it is possible to find out what was the pre existing date?
View 14 Replies
View Related
Feb 6, 2007
In this db, the users select their name from a dropdown in an auto-open form, but with no password. I want to capture this value for use as the default value in a field on another form, but with the first form closed. I have seen the ENVIRON(Username) function referred to many times on here, but I need to capture the entered value, not the Windows or network login.
Is this possible without leaving the form open? I suppose I could just set its Visible to False when they enter their name, but I try not to have any more windows open than are necessary.
THANKS for your help!!
View 8 Replies
View Related
May 18, 2006
I have done some searching to find a method for logging & timestamping when users log in and out. I have no problems with capturing the Log IN information my problem lies with the log out portion where I am trying to use a Update query to update when the user logs out of the database:
I can get the update query to do what I want, but for some reason it is creating a duplicate record (exactly the same as the last record updated)
Here is the code I am using:
UPDATE LogInTable SET LogInTable.LogOutTime = Time()
WHERE (((LogInTable.LogOutTime) Is Null) AND ((LogInTable.UserName)=LogUserName()));
Basically I am trying to tell the Query to ONLY update records where the "LogOutTime" is Null AND where the UserName field in the table = the username of the person trying to run the update query.
Please help I can't figure out how to prevent these duplicates. :(
View 9 Replies
View Related
Mar 12, 2014
I trying trying capture the ID no. of a record for future use. I have tried different combinations on different form events. Nothing works, what am I doing wrong? The latest I have tried is the following, on the load event,
Code:
Private Sub Form_Load()
Dim LoadNo As String
DoCmd.GoToControl "ID"
LoadNo = Me.ID
txtLoadNo = LoadNo
End Sub
View 7 Replies
View Related
Jan 30, 2014
So, I'm very new to Access and not all that skilled in VBA. I have, through trolling the internet found some code that does allow me to send an email on the click of a button on a form through Lotus Notes.
However, I cannot get any of the data on the form to show up in the email.
How do I even do something simple like, have the subject line come from a text box on the form or have the body come from a text box (or 2)?
Here is the code I have so far - also, the "attachment" part does not work - but one problem at a time...
----
Public Sub Command15_Click()
'-------------------------------------------------------------------------------
' Date: 02-06-2003
'
' Sends an email via Lotus Notes.
'-------------------------------------------------------------------------------
'Set up the objects required for Automation into lotus notes
Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'THe current users notes mail database name
Dim MailDoc As Object 'The mail document itself
[Code] ....
Again, I literally just have this as an event on "On Click" for the button. Nothing too fancy. It sends an email....with whatever text I manually put in to this code....which is not ideal....
View 9 Replies
View Related
Oct 6, 2013
I'm currently developing an MS Access (2010) application and connected a barcode scanner to it for reading barcode information.
Everything seems to work well for what concerns capturing the data into a field, but what I would like to do is to populate a complete form once the barcode has been captured and filled-in in the specific "barcode" field. The data that should be populated in the form comes from different tables.
How to send an enter after the barcode data has been captured? Objective is to read data from a barcode, which after an enter or update automatically populates the data in the form afterwards.
The barcode data is already in the DB.
View 14 Replies
View Related
Mar 19, 2012
in a query, I would like to capture data that is in the following format:
123456 (6 digits) AND
A12345 (1 letter and 5 digits) AND
AB1234 (2 letts and 5 digits)
The letter could only be at the beginning of the abbreviation.
View 1 Replies
View Related
