This works but the problem i have is, when i add an entry via a form or do anything else via a form. This does not get updated?. I check the properties of the table and that isn't either.
When i then modify something manually in the table (go in and change it). It then updates.
Is there something im doing wrong or is there a better way to check the last updated.
Hi, I have a form which is made up for 3 tables and I am trying to create an append query for each one in order to keep records of data before it is updated. The append queries seem to work but they append all data rather than just one selected record. I know I will next need to create a macro which can be used each time a record has been updated and a copy is sent to the archive. Can anyone help me with this, or have any useful suggestions?
I have a database that stores information for lab testing. Each time a tech does a "step" in the test process he logs it in the table, using an input form. There are different categories, for example preparation, testing, analysis, etc, and each of those steps take time. I have the form autopopulate the date and time with NOW() evertime the form is updated. What i want to do is calculate the time it takes to do each in days. I can easily get how many days it was from now since they logged the test, =NOW()-TestDateTime. What I want to do is get the number of days it took to do each step, ie the number of days between each event. Is there a way to do this?
My office workstation took a very long time to connect to database in Access 2003 after upgrading to Windows XP, a few minutes need to be taken just to open a simple form which normally takes less than 10 seconds. In simple words, whenever it has to retrieve data from the db, it will be super lagging .
The rest of the workstations running on Windows 2000 were running perfectly fine.
The database is located on the host pc, which is running Windows 2000.
Initially we suspected that it might be because of the insufficient RAM. Thus it was changed from 1GB to 2GB. But even after changing to 2GB, the problem still exists.
Can anyone please advice me on how to check and rectify this problem? What can be the cause of this problem?
Displaying the 'Last Modified' dates and time on a form. How to go about doing what I want to do.
I would like to display on a form in one single field the date & time that any of the fields on that form were changed and. I would like to display it in such a way that it cannot be altered by anyone. If it is possible to capture and display on the form in a separate field which user account accessed and changed the record then all the better. Obviously if nothing is updated then the date and time stays the same.
I will have to create two new fields in the table that relates to the form and this I have done - one of format Date/Time and one of format Text.
Is it possible for a query to automatically get updated each time a table is added. For example I am looking at prices and each month a new table of information is added for that month and I use a query to view each month next to each other. Is there a way that each time I add a table the query will automatically update?
I am trying to produce a database that will select a swimmers, best time for each event, and will select the overall best time done by any swimmer in the events.
At the moment I am having some trouble with my queries.
When trying to get the query to select a swimmers best time for the events, It will work when selecting min for the time field and including the following fields: Forename Surname Stroke Distance Age group Gender
However I also wish to include the name of the swimming pool the time was done at and the date at which it was achieved. My problem is that when I add these fields into the query, it looks for the swimmers minimum time in the event, on the date, and at the venue and consequently comes up with all the times the swimmer has done, rather than just the best ones.
I would Really appreciated if anyone had any suggestions as to how I can get over this problem
I have the following code to run search: Code:Private Sub Command20_Click() Me.household_id.SetFocus 'Me.Text21.SetFocus 'subj_num = Text21 DoCmd.FindRecord Me.household_id, acEntier, True, acSearchAll, True, acCurrent, TrueEnd Sub Let's say it found 5 records matching the search criteria. I like the Access form show me each record every on second. Meaning show me next record every one second.
I have the follwoing code to loop throgh each record but I nned to run after the find code runs and loops throgh the record that have been found:
I have the following code to do the loop through each record but, I need to run it after running the find code and then loops through the records that have been found:
Code:Private Sub Command10_Click()Dim inti As Integer'Loop through record 1 to 20 in form For inti = 1 To 20 DoCmd.OpenForm "DEM_RECH_CHECK_LIST", , , "[household_id]= " & inti Next inti End Sub
I have a database that is used (partially) to enter appointments during a day. In the appointment column I've entered the date and time as dd-mmm-yyy-hh-nn-ss. I'm now trying to search for appointments entered on a specific day through a user input ([Enter Date] in the query criteria) Problem is, this only returns records where the time hasn't been entered and the time shows as 00:00:00.
I've tried CDate which gives me add/mmm/yyy return. if I try to search against that I get nil returns though. As a test I put DATE() in the criteria but it then says that it's an invalid foremat. I've also tried various machinations of "Like" and "Between" without success.
It is basically a DCount, and it should find records, but returns 0 all the time. My code is:
Code:
Function cntkit(sftd As Date, sftn As String, typid As Integer, specpaint As Boolean) As Integer 'Counts jobs kitted during shift given by sftd and sftn Dim timeformat As String timeformat = "#mm/dd/yyyy hh:nn:ss#" 'need this, to convert it to US datetime format cntkit = DCount("[JOB]", "Archive", "[Type] =" & typid & " And [Autfinish]=False And [SpecPaint] =" & specpaint & " And ([Kit] BETWEEN " & Format(sftstart(sftd, sftn), timeformat) & " AND " & Format(sftstart(sftd, sftn), timeformat) & ")") End Function
sftstart and sftend are functions which are returning dates. The funcion works fine if I omit the Between part of the criteria. So the problem is in that part.
I have a database with a form called "Main" where users input data and then print a report from it. "Main" has fields in it from another form "Members". This data (from "Members") is shown on "Main' by Dlookup coding, and therefore cannot be selected for input by the user. Now, lets say a user inputs data into "Main" and prints the report on 12/30/2012. On the next day, a member's name is changed and I update that data in the "Members" form. On 12/31, I would like to print the report again, but it shows the updated member's name instead of what is was like on 12/30. How can I keep the old data in case I want to print the report in the future like it was initially printed? What do I need to do to any form(s), report or what VBA code needs to be written?
Hey guys im stuck on this one. I need a fax # to be changed from 1 format to another. i dont even know where to start any help would be appreciated. the original data is already in a table like so; (999)#000-0000 and i need on my final table or query all the records to be:9990000000
I have been stuck on this one all weekend long, can it be done. thank you.
I have a very simple database used by 3 users at a time. It is split into BE and FE. One user accesses the DB with queries only so I have created a DB with the master file linked - and literaly nothing else, She will then create and save all of her queries here. But - I want to keep her from accidently updating the file from here. I can't seem to find a simple solution to simply not allowing her to update. Any ideas? Thanks - Dika
This one is driving me NUTS! I have a form with a combobox, a few textboxes, and a sub-form. On Load the form is populated with a sql command/rcSet.Fields() results. The user then chooses a status from the combobox to narrow down the results. The combobox OnChange event looks like this... sDate = Forms("frm-MENU").txtS_Date.Value eDate = Forms("frm-MENU").txtE_Date.Value xJob = Forms("frm-HD/DVR_CC").lstJob.Value
On Error GoTo hd_dvrErr 'this gets the top of the HD/DVR form sSQL = "SELECT Count(*) AS Total, Sum(IIf([pmt_meth] In ('C','E'),0,1)) AS Error, Sum(IIf([pmt_meth]='C',1,0)) AS [Credit Card], Sum(IIf([pmt_meth]='E',1,0)) AS EFT " & _ "FROM [tbl_HD/DVR_CreditCard(*)] " & _ "WHERE ((([tbl_HD/DVR_CreditCard(*)].CDATE) Between #" & sDate & "# And #" & eDate & "#) AND (([tbl_HD/DVR_CreditCard(*)].JOB_TYPE) Like '" & xJob & "*'));"
Set db = CurrentDb() Set rcSet = db.OpenRecordset(sSQL)
With Forms("frm-HD/DVR_CC") .txtTotal.Value = rcSet.Fields(0) .txtError.Value = rcSet.Fields(1) .txtCC.Value = rcSet.Fields(2) .txtEFT.Value = rcSet.Fields(3) End With
'this gets the bottom or subform of the HD/DVR form sSQL = "SELECT IIf([Agent]='UNKNOWN','xxxxx',[REP]) AS [Agent ID], [tbl_HD/DVR_CreditCard(*)].Agent, [tbl_HD/DVR_CreditCard(*)].JOB_TYPE, Count(*) AS Total, Sum(IIf([pmt_meth] In ('C','E'),0,1)) AS Error, Sum(IIf([pmt_meth]='C',1,0)) AS [Credit Card], Sum(IIf([pmt_meth]='E',1,0)) AS EFT, Sum(IIf([pmt_meth] In ('C','E'),0,1))/Count(*) AS [Error Rate] " & _ "FROM [tbl_HD/DVR_CreditCard(*)] " & _ "WHERE ((([tbl_HD/DVR_CreditCard(*)].CDATE) Between #" & sDate & "# And #" & eDate & "#)) " & _ "GROUP BY IIf([Agent]='UNKNOWN','xxxxx',[REP]), [tbl_HD/DVR_CreditCard(*)].Agent, [tbl_HD/DVR_CreditCard(*)].JOB_TYPE " & _ "HAVING ((([tbl_HD/DVR_CreditCard(*)].JOB_TYPE) Like '" & xJob & "*')); "
Set qdTemp = db.QueryDefs("qry_HD/DVR") qdTemp.SQL = sSQL qdTemp.Close
If Not Forms("frm-HD/DVR_CC").FormFooter.Visible Then Forms("frm-HD/DVR_CC").FormFooter.Visible = True Else 'DoCmd.Close acForm, "frm-HD/DVR_CC(Footer)", acSaveNo End If DoCmd.OpenForm ("frm-HD/DVR_CC(Footer)"), acNormal, , , , acHidden Forms("frm-HD/DVR_CC(Footer)").Recalc Forms("frm-HD/DVR_CC").Refresh Forms("frm-HD/DVR_CC(Footer)").Refresh ' DoCmd.MoveSize Height:=Forms("frm-HD/DVR_CC(Footer)").WindowHeight + Forms("frm-HD/DVR_CC(Footer)").FormFooter.Height
I have used similar code on another form and everytime the search criteria changes the sub form updates to reflect such. I know I am missing something small; can somebody please point it out?:D I need to have the subform show the updated (choice from combo) criteria.
If this seems to cloudy, please let me know and I will try and revise
I am running repeated calculation queries of different data, but its is not working to well. I have one query listing the total number of hats made from wool. I then have a second query of that lists the total number of hats made from wool and are black. I have third query that calculates the percentage. I have many different hats of many different colors, and i am doing the same three queries over and over. However, the calculated field for percentages does not update. E.G. I have 100 wool hats, 10 wool,black hats, 10%. The next round: i have 100 wool hats, 5 wool green hats, its will still show me 10%, as its is still calculating based on the previous data. how can i go about this most efficiently and why is not updating?
I maintain a shared database that I would like other users to be able to see when the data was last updated when they open it so that they can see how up to date the information is from the switchboard.
Currently I am doing this manually by updating the date in a label on the switchboard header, but ideally I would like to add a final action to my update macro (that imports new data into the database) that would automatically do this for me on completion if possible.
I have developed an application using MS Access and MySql tables. The Access forms maintain/display orders that are stored in the MySQL linked tables. The MySql tables can also be updated independently by another process. The problem is that the Access forms do not reflect the backend changes when they are made by the other process.
For the moment I have implemented an on Timer process that causes a requerys for the forms every 5 seconds but this is not very elegant and causes the form screens to flicker.
Is there a better way I can automatically make the front end forms reflect changes to the back-end database when it is updated indepenently ?
Hi, I have a VB6 application talking to Access DB. The program was suppose to do some audit tracking but due to a bug it didn't create data in Audit table. Is there a way i can retreive data which is updated (Old values) for 2 tables. If there is a tool/utility i can buy please let me know.
I've been making small changes here and there to my database in Access 2013. I have left the original database in place and it has acquired many new records from the last month. I'm ready to start using the newly updated design version of the database, but I do not want to have to type all those new records into the new database to get it up-to-date. There has to be a way to import the data from the original database to the newly updated one.
Most, If not all of the fields have the same names, but some of the fields have changed, like to Combo boxes. I removed two or three unneeded fields, and added two or three. I understand the new fields will need to have data enter to them, but the remaining would be redundant and inefficient if I have to reenter all the recent records again.
How do I import the data from the original two tables to the new updated database? I only have two tables and they use a One-to-One relationship in both databases. The table's names are different, but as I mentioned earlier, the fields are mostly the same. Do I need to import into Excel, and then modify the data slightly, and then import into my new database?
I have developed a very basic contact DB to record enquiries coming into my business. The last field on the DB is for comments, so that anyone entering the DB can see the latest conversation we have had with a particular customer. What I want to do is create a means whereby I can find a customer within the DB and then be able to add the contents of our last conversation into the Comments field. Would there be any way of doing this via a switchboard??? I am a very inexperienced Access user so the simpler the answer the better. Many thanks in anticipation Keith:(
I have a report that is pretty complicated in the page numbering, since it's grouped by Aisle Number (it's a report of hazardous products in a store), and the Aisle Number and the "Page x of x" is in the header.Someone here actually created all of the logic for me last year.how a particular table is being populated with the information.Here's the Event Procedure for the "On Open" event of the report:
Code:
Private Sub Report_Open(Cancel As Integer) 'when the report opens the temporary table needs to be cleared CurrentDb.Execute ("Delete * From ztblAislePages") 'delete records from table ' the recordset object needs to be opened so it can be used and accessible in the group footer and header sections Set GrpPages = CurrentDb.OpenRecordset("ztblAislePages", DB_OPEN_TABLE) 'open the ztblAislePages recordset GrpPages.Index = "Aisle" 'set the index so procedure knows what field to search
[code]....
ztblAislePages is a permanent table that holds temporary information...it's populated with information only for this report, and as you can see from the code above, it's cleared at the beginning of the process.where this information is coming from to populate the table, however.
Code: Set GrpPages = CurrentDb.OpenRecordset("ztblAislePages",DB_OPEN_TABLE)
The table itself has no source data that I can find, unless I'm not looking in the right place.I've done a search for dependencies and can't find anything. All I know is that when I choose a store, the table IS being cleared, and it IS being populated with new information.
I am trying to find duplicate data from two columns and I want to query the all the duplicates. I tried the query wizard but it only finds duplicates in the column itself. I am trying to compare the columns together and see what is found in column A that is also found in column B and also Vice Versa.
I have a table called SWAPS which contains information on each user and the different equipment he has been issued. Query at the moment contains 5 fields
Payroll, Surname, Firstname, Mobile No and Date sent.
SQL for this query is as follows
Select Swaps.PR, Swaps.surname,swaps.firstname,swaps.[mobile no],Max(Swaps,[Date Sent] as Lastdate
From Swaps
Group by swaps.pr, swaps.surname,swaps.firstname,swaps.[mobile no]
Order by swaps.pr;
Now when I run this I get the results I want by date so I get
123456 Smith Fred 0788411025 12/3/8
456789 West Bob 012555447854 13/1/8
So it correctly ids all the last dates for me as I want to see the latest issue date for that user. Now I want to add in another column to this query which contains the serial number of the equipment issued and heres where the problem lies. If a user has been issued more than 1 piece of kit, I get all the serial numbers irrespective of date. How do I get it to display the serial number for the latest date?
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still shorttime but when you read the time within the code it comes out in AM/PM format. Also, when the defaul tiem should be #20:00:00# but this changes to #8:00:00 PM#
Weirdly though, when you just open the table, the times are in the correct shorttime format.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
