Sync A DSUM To A Record
Oct 13, 2005Hi
Could someone help with showing me how to sync a DSUM to a record as i am currently summing all the records instead of the ones i need?
Thanks
ADVERTISEMENT
Record Selector Sync
Aug 27, 2006I have a table with fields ID, NAME, ITEM, QUANTITY and DATE_IN. I have a query "qry_Logged_In" based on the table. I then have a form based on the query.
I created a combo box as a "go to record" selector and used the following Row Source:
SELECT DISTINCTROW qry_Logged_In.ID, qry_Logged_In.NAME, qry_Logged_In.ITEM, qry_Logged_In.QUANTITY, qry_Logged_In.DATE_IN FROM qry_Logged_In ORDER BY [ID];
On the After Update proceudre I have the following code:
Sub Combo135_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo135]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub
It works fine, but one thing I would like is that as I toggle through records using the record selector (forward/backward) buttons, I would like for the record number in my combo box to also change keeping in sync with the record number. Unfortunately, it currently stays to the number I last selected in the combo box.
Any suggestions on how to make the record number showing in my combo box to change with the record number?
Thanks,
Jim
Queries :: DSum Always One Record Behind
Nov 18, 2013I am using DSum to total some records located in a subform. I have code to save the value returned by DSum to a table on a lost focus event. It works great except for one thing. The value saved is always one record behind. The save code is:
Dim dbs As dao.Database
Dim rst As dao.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblInvoice", dbOpenDynaset)
rst.FindFirst "ID = " & Forms!frmInvoice!txtID
With rst
.Edit
!Total = Forms!frmInvoice!txtTotal
.Update
End With
Exit Sub
txtTotal is the textbox containing the DSum value. It displays the proper value. If I insert a blank row the routine will finally pass the right value. I tried running the code from a button but it still copies the old total.
Queries :: DSum - Count Of Each Time The Record Appears
Feb 26, 2014I'm trying to generate a query that can be used for a pareto chart (Bar Chart shows the count of a defect and a line chart as a second axis counts the cumulative percentage)
I've grouped my data, and sorted the Count of each time the record appears but I cant get my head around working the cumulative percentage. My datasheet currently looks like this:
Reason Count Per Expr1
A 35 47.9 Random Numbers
B 11 15.1 "
C 10 137 "
D 9 12.3 "
E 7 9.6 "
F 1 1.4 "
Expr1: DSum("Per","Rwk_Pareto","[Per]<=" & [Per] & "")
Need Help With Subform Sync
Feb 23, 2005Hello,
I am really stuck - I have a form of companies with a subform of contacts.
In the DB (SQL) the tables are related one to many CoKey to ContactCoKey.
How do I synch these so any new contact record automatically gets a "new" ContactKey as well as the related ContactCoKey?
Thanks,
Cant Get Pop-up To Sync With Sub-form?
May 31, 2005Hi Gang,
I am using Access 2000.
I have a Main form that displays a sub-form. On the sub-form is a image box that displays a jpeg associated with the current record. The jpegs are stored outside of the db as links and the links are stored in a table in a field called Image1Lrg_Path. Due to the GUI design the image box is a set size = 4cms X 5cms.
On the sub-form I have a cmd button that when clicked should open a seperate pop-up form that *should* display a large version of what ever jpeg is being displayed on the sub-form. The user would then have to close this pop-up to return to the db in order to continue scrolling through the records as normal.
Main Form = frmPlant_Main
Sub-Form = frmPlant_Sub1
Sub-Form Control = Garden_Sub
Image Box = Image1
pop-up form = frmLargeImage1
pop-up Image Box = Image1Lrg
Here's my problem:
If I set the pop-up form up a normal form and have a linked control on it (to display the image) and a text box (in which to type in the image path and name) and a cmd button that, when clicked, sets the image box to the value of the text box. Then this all works great, what ever jpeg file name I type in the text box, providing it exists of course, is displayed when I click the command button. Iset it up this way first as a test to see if I could get the image displayed OK.
Just for reference, here is the code I use for the command button;
-------------------------Code Start---------------------------
Private Sub Command1_Click()
On Error GoTo Err_Command1_Click
Me!Image1Lrg.Picture = Me!Image1Lrg_Path.Value
Exit_Command1_Click:
Exit Sub
Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click
End Sub
----------------------Code End--------------------------------
Now, if I try to link the pop-up to the image being displayed in the sub-form Access throws up "Can't find the form" errors.Just while I set this up, I am using the cmb button to set the path but in the final db I will want the pop-up form to select what ever jpeg is being displayed on the sub-form as its path.
Can any one suggest how I can get this to work?
Again, for reference, here is the code that I am trying to use when linking the image in the pop-up with that shown on the sub-form;
-------------------------Code Start---------------------------
Private Sub Command1_Click()
On Error GoTo Err_Command1_Click
Me!Image1Lrg.Picture = Forms![frmPlant_Main]![Garden_Sub].Form![Image1_Path].Value
Exit_Command1_Click:
Exit Sub
Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click
End Sub
----------------------Code End--------------------------------
My grateful thanks in advance,
Kenny
Sync With Computer Clock
Jan 18, 2006Hi,
My question is...is there any way to get Access to work out the actual day of the week from a date entered into the computer? The reason for this is...I am designing and implimenting a bookings data base for a village hall, and I wish access to automatically store what day of the week it will be on the specified date, in a field called 'Day'. I need this field to produce a report for the week ahead, showing exactly what is in on which day of the week. Is there any way this can be done? Or do the days just have to be typed in manually, i.e. check a calendar and type them in? I would appreciate it if anyone could give me info...on ANY way this can be implimented...be it store the dates in a table or use a giant Iif statement or whatever! And the tags necessary would be helpful,
Thank-you for your help in advance, and i apologise for such a lengthy post!
Please get in touch directly through c.majury@ntlworld.com or alternatively post the answer here.
Chris
How Do You Sync An Unmanaged Replica?
Mar 14, 2008I need the same database as managed and unmanaged in repl manager, I think.
My replica set got scrwd up and now I need to re add an unmanaged replica.
Previously the map displayed a scheduled synchronization line to the replica not managed by synchronizer.
I can add the unmanaged replica, but how do I add the synchronization line?
Do I need the same replica added to the map 2x? 1 managed and the other unmanaged?
i tried uploading 2 bmp files to show the way it was and is now but i dont see them, how do u add bmps to post?
Sync Two Forms To One Table
Mar 16, 2006Does anyone have any idea how to sync two forms so that I can create one record in a table from the pair of them.
I have a form which is so big that I have had to place a continuation to a new form on it. However, when I move to the new form, the data is placed into a new record instead of continuing on from where it left off.
Someone please help
Regards
Jason
Sync Tabbed Form
Feb 25, 2008I have a Tabbed form where one of the tabs shows about 20 rows of a table. Another tab shows a single record from that table with all the fields for that record.
I want to be able to move from tab to tab and and stay on the same record (hopefully that's clear).
What is the cleanest approach... any suggestions?
Thanks
How To Sync Up Fields Within Databases
Feb 23, 2006I have written a VB.net application that uses an Access database. I have a table that displays the following information.
Customer Name
Customer Phone
Customer Address
Shipping status
Now in my vb.net application I am displaying these 4 things in a data grid. But here is the problem. The shipping team updates their database daily with the new Shipping status. So to get the latest shipping status I created a linked table, lets call is link_shipping. Now All I have to do is somehow get the shipping status field from link_shipping and update the respective table in my database with this info.
To do this I created a query. but now I am having to manually go into my database and run this query to make sure the shipping dates sync up. How can I do this in a better way. The only thing I can think of is to find some way to automate the running of this query every day. But I dont know how to do this. Can anyone suggest anything here.
Linked Table Not In Sync
Jun 25, 2014Yesterday my work's IT department moved a network drive to another location. The old location still worked as read-only. I have two Access databases: One contains the tables and the other contains the forms and queries, and has links to the tables in the other files. In order to avoid having to use the linked table manager every time I move the databases, I use the networkaddress notation instead of drive letters, such that the database with the linked tables was pointing to the old, read-only location. I deleted the linked tables and relinked to them using the new location, and noticed that there was a discrepancy of 30 records I had entered through the forms not showing up once I linked to the tables in the new location.
It was clear that the database I use to enter data never actually inserted those records into the linked tables, but still acted as though it had in that it showed all records. Once I deleted the linked tables and linked to them again, those records completely disappeared. What's odd to me is that I'd entered these data into the database over a week ago. Why does Access take so long to synchronize the data between the linked tables and the actual tables? Are there any settings I can change to make sure the data are synchronized when I exit the application? Is it the ODBC refresh interval? If the default is 25 minutes (1500 seconds), does it mean that the data are never synchronized if I don't keep the database open for at least that long?? This is really puzzling.
I was able to go back to the old network location and get the old database showing all the records it's supposed to have. I had it import the linked tables as local tables and it looks like the local tables contain all records. This is not an ideal solution, but at least it seems like I recovered all the data.
Modules & VBA :: Force Windows Sync To Run
May 7, 2015I have an application that is using SQL Express as a backend and syncing to a SQL database through SQL web synchronization. This all works great and my clients have my custom application to interface with their local database and do work and it syncs back to the corporate database and I've got it scheduled to run in Windows every minute. What I would like to do is have a button or on event set of code that forces that sync pairing to run and I'm not exactly sure how to do that.
View 3 Replies View RelatedGeneral :: Sync Tables When Connect To LAN
Nov 24, 2012I want to make a database on my company server. There will be two tables. "Customers", and "Visit to customers".
There are 5 sales managers in my company and i want them to collect information about customers on the field.
My question is:
How to sync Customers and Visit tables from laptops with Customers and Visit tables on the server?
Can i do it over internet or i can make a button that will sync tables when sales managers come to the office and connect to lan?
General :: Background Sync Of Data
Dec 11, 2014I am creating an application that is using Access as the front-end for data entry, some manipulation, etc. In the example, my guys will get a ticket and then stamp that ticket with times of when things are done. The way I'm doing it now is that I have a second Access application that is working as the synchronizer. It just sits in the background and syncs the data.
The issue I'm having is that the guys are on mobile devices so if/when they lose connection to the database (they are constantly moving in trucks), Access doesn't do a great job of reconnecting to the tables, even if you drop the tables and re-add them. I've attempted to close the sync app from the main app and then re-open it but this just results in the sync app being in the foreground and I need something that sits in the background and is hidden.
Modules & VBA :: Sync Between Recordset And Access Table
Oct 15, 2014I have a normal Access table. The first column is ID and is the primary key. The second column is a text. The table consists of two records and is sorted in ascending order. First comes the data set with the ID 1.
Sub test()
Set rs_access = CurrentDb.OpenRecordset("tab1")
rs_access.MoveFirst
MsgBox (rs_access.Fields("id").Value)
End Sub
The message box shows 1. So everything is fine. Now I sort the table descending. But I get the same message. Why? Appearently the recordset doesn't know that the table was modified. The recordset should register that the table is modified.
Sync CheckBox To Update/Write To Text Field
Jul 14, 2006Hey Guys,
I'm looking to create a "Same As Billing Address" check box that would automatically udpate the address in an order to reflect the customer's billing address.
I understand how to sync a combo box to an option group ( -- great article by the way)
I Guess what I'm looking to do is:
1. Sync a "Same As" Checkbox to multiple text boxes in the form (Unit, StreetAddress, City, Province, Postal) such that these fields are UPDATED (writen to) with the same information as the billing address
2. [U]IF the "Same As" Option is selected, I need the updated fields to lock, allowing no further update... but must by unlocked if the "Same As" option is NOT selected.
Please help me out with this one guys.. I really need it.
General :: Autonumber Is Out Of Sync After Upgrading To Access 2010
Aug 16, 2012We have been using a shared database (on the server and it's not spilt) that was converted to MDE file and was created in Access 2003, without any issues for more than 5 years now.
Since we upgraded our office to 2010 (and access 2010) all the sudden the autonumber field starts generates a number that is about 40 to 50 records less than what is should generates. All the records for the missing 40 to 50 numbers are still there and we can search them.
The only work around is to run the database and open a new data entry form (at which time the autonumber field will generates a new number) then close the form, and we keep doing that until we reach the number that should be generated next.
Forms :: Sync Subform Data With Main Form
Feb 17, 2015I have tblEmployees for employee info
tblEmployeeAbsences for employee absences
tblVacation for employee vacation info
While entering the employees hours I want an adjacent subform to show the matching employees absence and vacation days taken if any.My approach was to use a Cartesian qry for the record source for the subform and link it with a fake key from the main form.
General :: Don't Have Option To Sync Tables After Working Offline
Dec 1, 2013I am using SharePoint 2010 lists as my tables in my access file.the users sometimes have no access to the sharepoint and I want them to sync their tables to sharepoint later once they do have access to the web.I have no problem doing it on the regular access file, but when I'm using the access runtime, I don't have the option to sync the tables after working offline.
View 1 Replies View RelatedButton On Main Form To Change Records Of All Subforms In Sync
Aug 29, 2012I have a main form and four subforms all of which were created from the tables without queries. The main form pulls employees personal data (never changes). The subforms pull Events attended, Lodging at the event, Family that attended the event and Employee minutia which changes per event. I'd like to have a button on the main form that changes the records in view for all of the subforms at once. This way it is easier for the users and I know that I am view all the pertainent info for event A instead of viewing event A's lodging info with event B's family info.
View 3 Replies View RelatedForms :: Main Form With Multiple Tabs - Sync Two Related Subforms
Jul 28, 2014I have a main form with multpile tabs - each tab containing a different subform.
Link Master Field: ClientID (field in Master Form)
Link Child Field: Client ID (field in all subforms)
There is another field that all the subforms (continuous type) have: ObligorName. All the subforms are based off of a huge table (subtable) with fields: Obligor Name, Address, Zipcode, City, DOcuments Required, Bank Account Number, etc......
Essentially, the user will fill in the 10 obligor names associated to ONE client on the first subform on the first tab + address + zip code + city.
When the user clicks on the second tab to fill out the next subform, I want all 10 obligor names to be there already, so then they can fill out Documents Required + BAnk Account Number.
If I fill out the first subform and then exit out of the form and then reopen it, the other subforms autofill.
HOWEVER, I dont want to have the user have to do this. It wastes a lot of time. What can I do about this so that the table is automatically updated right away. They are all based on the same table so I do not see why there is so many issues.
Should I Use DSUM?
Jul 30, 2007I want to add to values based on if yes or no is selected in an option group.
My thinking is to use DSUM. When 'yes' is selected I want to grab a value from one field and add it to a value in another field giving a running total. I am currently using the following code but it throws back an error. Is DSUM the way to go?
Case 1
txtTotal.Value = DSum("[s1]", "[s2]")
Using Dsum()???
Mar 29, 2008Hi there guys, ill keep it short and snappy,
Im trying to use the Dsum() function to add together the "Quantities" of specific products bought in transactions i.e. the total quantity for a product called "red t-shirt" and the total quantity for another product called "blue t-shirt". the following was the code i tried to use but it just adds together all quantities of all products:
DSum("[Quantity]","Query2","[Product Name] =[Product Name]")
Does anyone have any ideas at all please?
Thanks
DSUM() I Think!
May 17, 2005Ok, I have a commercial database over which I have no control. It is ticket sales and I want to view them over time...
This is my table of transactions:
master_tix_header
tix_header_year
tix_header_performance_code
tix_header_no_seats - The number of seats sold
tix_header_first_transaction_date - The date of the transaction
The unique ID of the performance is made up of tix_header_year and tix_header_performance_code:
E.g.
In tix_header_year of 2004 there is a tix_header_performance_code of JSVH01 and in 2005 there is also JSVH01 - but there is never the same tix_header_performance_code more than once in the same tix_header_year...
Now I can get the number of tickets sold on each day using Sum() for a specific performance using:
SELECT SUM(tix_header_no_seats) AS TotalSeats, tix_header_first_transaction_date
FROM master_tix_header WHERE
tix_header_year = "2005"
AND tix_header_performance_code - "JSVH01"
GROUP BY tix_header_first_transaction_date;
This gives me:
TotalSeats tix_header_first_transaction_date
2 14/03/05
9 18/03/05
1 22/03/05
But what I want is:
TotalSeats tix_header_first_transaction_date
2 14/03/05
11 18/03/05
12 22/03/05
Can anyone give me any pointers?
I tried http://support.microsoft.com/kb/138911/ but it returned ambiguous results (seemed to reset the running total at the begining of each year...) or would say the query was too complex to run - I;ve probably missed something simple as I a newbie to the world of access/sql
TIA
Nick
PS: Access 97 if that makes any difference!
DSum Help
Jul 5, 2005I am trying to add up the total amnt under a certain location # under a certain representative #. What im having the query do it pull information from several different tables including the rep # and that rep #'s loc #. I want to find the sum of the sales under the location # for that rrep #. So it will look it up based on the loc # after it finds the rep #'s loc #. What would be the best way to do that.
View 1 Replies View Related