I have a form entering records into a table. The only way I can seem to get the new records entered using the form to appear in the table is by closing it and re-opeing it. Is there a way I can get the table to refresh with new records automatically?
Thank you
I have a mainform with 4 fields which are marked with 4 primary keys: soldtoId, shiptoID, PlantId and formulaID. PlantID is a long integer number field and the other 3 are text fields. there is no default values.
I have a subform1 that is linked by PlantID and FormulaID. These 2 field names are in the linked child fields.
I had a handful of records established already. Today, i added a command in the after update section of the PlantID field in mainform by adding Me.Refresh.
So when i change the plantID in the mainform, the subform gathers different plantID and formulaID data automatically. works nicely.
Here is the rub. I next tried adding NEW records to the mainform. that's the one with 4 primary fields.
i enter soldtoID and shiptoID fine. they have no refresh need. however, after the enter a PlantID, the me.refresh kicks in before i can get the the formulaID field. i get a debug error. something about a NULL. which i suspect is the formulaID that i didn't get a chance to enter to. FYI, later, i want to put a refresh on formulaID too.
so how can i make some kind of Refresh work for both New and existing records??
penwood
Background:
I have a continous form linked to a table and I have 3 combo boxes on the
form (linked to fields on the table). The combo boxes are called JOB TITLE,
SCHEDULE and SHIFT. The source for the combo boxes are queries named
respectively as LKUPJOB, LKUPSCHEDULE and LKUPSHIFT. For the three combo boxes listed abpve, the bound column is the first field of the underlying query and the column width of the first field is set to 0" (so that the second field of the query is displayed).
The way things should work is that for each record on the form, the
LKUPSCHEDULE and LKUPSHIFT queries (queries for the SCHEDULE and SHIFT combo boxes), the queries are filtered based on the bound field of the JOB TITLE combo box. The name of the bound field for the JOB TITLE combo is called Labor_Rate_ID.
Problem:
Unfortunately, It seems like the value of the labor-rate_ID in the first
record (i.e. first job title combo box) determines the query list used by the
SCHEDULE and SHIFT comboboxes for all the other records on the form. That is, even though the form has lots of records displayed, the combo boxes for each record don't have a complete list to work with.
Hence, the SCHEDULE and SHIFT combo boxes for some records are showing a blank (since available otions in the combo query list don't match the value
in the table for that record.
Can anybody tell me how to get a continous form to refress the list for each
record on the form.
I also don't know whether the Requery command for a macro could work and how to use it. I think though, that this problem can't be solved by a macro.
Basically, the question is around what kind of code will let you manipulate the properties of individual controls of a form at the Record level.
Pele
I have a form that displays records from a table.
When opened it displays all records (no selection yet).
I put would like to put 2 buttons to filter records (Male/Female).
I'm thinking of 2 possible options:
1-on click run a script that will close current form, then reopen it with selection criteria
2-directly apply a filter on the current form that would automatically refresh itself with the right records
Another (heavy, inelegant?) solution would be to create duplicates of this form. they would be loaded from separate queries that select the right records in either choice.
I added several records in another table but it wont show the records until I press the Refresh button at the ribbon of Home-> Refresh. How can i view the records automatically without pressing the Refresh button?
View 2 Replies View RelatedI'm trying to use a PivotTable Form (in Access), in order to automate a process (previously data was exported to Excel and the pivot table was created manually). I've got my pivot table form working fine but I need to provide the user with a way to refresh the Pivot table - i.e. re-run the underlying query.
In Access Help it says to select design view and then click the Refresh button (the one with the big red '!' icon). This does work and the data is updated but it's not a user friendly option.
Opening the form doesn't automatically refresh the pivot either, so does anyone know a way I can provide a button with VBA code or whatever to manually update the pivot so it reflects changes in the underlying data?
I have a form, containing a button that when clicked, it runs a query and displays the results in Pivot Table view. The query is set to display only results for the manager name selected on the form. So, when someone opens this database, they first select their name from a drop down box and then click the button to run the query. The query displays average call rate for equipment that person is responsible for. The only problem I am having is that if someone goes back to the main form, selects a different name, and clicks the button again, the query will not refresh. The query won't even refresh when I click the Refresh All button in the Ribbon. In order for the query to update, I have to first click the button to refresh the pivot table and then click the button to refresh the query. Is there a way to do these two steps through visual basic, so that the user doesn't have to do those two extra steps?
Right now, I am using the Docmd.OpenQuery code on my button.
I have a database in access that i have shared it and 5 person write in it. I want to show the datasheet in a monitor but my problem is hat I cant use auto refresh the Table datasheet. How can I auto refresh the datasheet of table when every client change it?
View 1 Replies View RelatedIs there any possibilities to open form after INSERT INTO? I think Ms Access can't fast refresh data in the table after that, so form opens up clean:/
Code:
...
strSQL = "INSERT INTO tblZlecenia (id_zlecenia_info, DataPrzyjecia) VALUES ('" & ostateczne & "', Date())"
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.OpenForm "Formularz2", WhereCondition:="ID_Zlecenia=" & ostateczne
I have a table called "EquipmentRequired" which is populated by 4 append queries,
5000BaseReq, 6000BaseReq, 6000IFBBReq, EquipmentReq which get some of their information by counting fields in another table but all have the same field names.
The queries contain all data that is initially used to append new records to the table and this works fine.Unless some information changes or a record is added then I would like to add a button to a form and call it "update equipment" behind which would run a vba code firstly to delete all the records in "EquipmentRequired" table then run the 4 queries without the warnings and re-populate the table.
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
View 3 Replies View RelatedI have a survey database that I've been using for the last year for monthly auditing of employees files. I need to be able to get monthly audit scores for each employee but grouped by their manager. The problem I'm having is employees have moved between managers throughout the year, so employees that are listed under Manager 2 now were actually working for Manager 1 when the audits occurred.
ie. Audits occurred Jan - April for Employee 1 while they were assigned to Manager 1. Employee 1 moved to Manager 2s team in May. So when running monthly reports for the year Employee 1 audits should fall under Manager 1 for Jan-April and Manager 2 for May-Dec.
I do have a history table set up like:
tblEmployeeHistory
ID (PK)
EmployeeID (FK to Employee table)
ManagerID (FK to Manager table)
MoveDate (date employee assigned to manager)
The Employee table is set up like:
Employee ID (PK)
EmployeeName
ManagerID (FK to Manager table)
The Manager table has the ManagerID and ManagerName.
That's the employee side of things; then I have the tables that store the audit results:
tblAudit
AuditID
FileNumber
AuditDate
EmployeeID (FK to employee table)
tblAuditResults
AuditID; QstnID (Composite PK, QstnID is FK to tblQuestions)
Answer
How can I use AuditDate and MoveDate to relate audits to the managers the employees were under when the audits occurred?
I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
I have built a qry that initially shows the correct information. For example.
tblContent has 289 records with a Type = Class.
I built a Query to select from tblContent Type = Class and I get 289 records. I add additional criteria of Progress <>"Not Scheduled", I then get 206 records. I then add additional criteria Last Name <>"Demo" And <>"Care" And <>"Support". This brings up 200 records, but the query appears to duplicate each record 3 times. I do not have 3 of the same types of records.
The SQL Statement is below
SELECT tblProfile.LoginName, tblProfile.FirstName, tblProfile.LastName, tblProfile.Organization, tblProfile.CostCenter, tblContent.Title, tblContent.Type, tblContent.Code, tblContent.[Date Assigned], tblContent.[Date Started], tblContent.[Last Accessed], tblContent.Progress, tblContent.[Date Completed]
FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName
WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Support") AND ((tblContent.Type)="Class") AND ((tblContent.Progress)<>"Not Scheduled"));
The qry is named qryPhysical Class. I have provided the link to view the database. Can you help me?
http://briefcase.yahoo.com/turnerbkgabrobins
Thank you in advance for your assistance.
I have a table InvPrice and Updated Pricing
Need to delete all records from InvPrice that Match UpdatedPricing
InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.StockCode
I have tried something like this...
Dim dbs As DAO.Database, sql As String, rCount As Integer
Set dbs = CurrentDb
sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "
dbs.Execute sql, dbFailOnError
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
What is the best approach?
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.
I have one table with the following field and data:
ItemId Red Yellow Green Multiple Inventory position
0001 10 30 50 5 45
0002 5 40 47 5 23
0003 11 20 30 10 5
I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:
ItemId Qty Start inv Aggregated inventory Prioritization
0002 5 23 28 Yellow
0002 5 28 33 Yellow
0002 5 33 38 Yellow
0002 5 38 43 Green
0002 5 43 48 Green
0003 10 5 15 Red
0003 10 15 25 Yellow
0003 10 25 35 Green
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click()
Dim strSQL As String
Dim RevisionDate As String
Dim RevisionRevisedBy As String
Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
I have two questions for someone
Is there a way to have the database auto refresh ?? If so how is that done
I use the service management template in Access. The problem we have is there a way to write a script to check for existing names when entering new ones. Like after entering all the information in a new record have it look at the first and last name and if it does exists have a alert come up stating that it already is there
THXS Steve
Hopefully this is an easy one...... Have a form... With two subforms. Info is entered into first subform... Second subform shows a recap of info entered. Kind of a running total of an "Average" based on info input on the first subform.
OK... Problem is... The second subform updates... requeries the info on close. I want it to update the info every time a new line or record is entered into the first subform. I have tried a couple of ".requiry"s on the first subform... but no luck. I'm not that hot with VBA yet.... And can't seem to get anything else to work. Solution????
Thanks
I am rebuilding an application for a client and I have an Access table that I am using as a temporary table. Once the user is done entering information into the temporary table through a form, the user presses an update button that appends the records using an Append Query in Access to an SQL Server Table.
The following error message occurs:
"ODBC -- insert on a linked table 'linked tblname' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Explicit value must be specified for identity column in table 'linked tblname' when IDENTITY INSERT is set to ON. (#545)
I am using a form, subform combination to record a bill with many details. The bill summary is posted into a tbl_TransactionsMain table in SQL Server using the ADO AddNew method. The PK for the tbl_TransactionsMain is then entered into the temporary table in Access. When the temporary table records are appended into tbl_TransactionDetail the error message occurs.
What is also interesting is while typing out this post I thought to test the error by manually trying to run the query. The query worked like a charm! :confused: When the orginal error occurred off of the form I tried to run the query manually and it failed. I am guessing that this might have something to do with the ODBC timeout.
I think SQL Server/ODBC connection is not liking how I have a set of records in an Access table with foreign key numbers assigned when I am attempting to append the records. I am new to SQL Server and any ideas are most appreciated! :)
Hoping someone can help me with this DELETE query. I have a Main table that's being updated by a Temp table that's an exact copy of the Main table but with a subset of records.
1) Insert records from Temp table NOT found in the Main table - this query I have worked out below - not tested, but the results look correct.
Need Help Here...
2) Delete Records from the Main that are not found in Temp table with an exception...only DELETE records where certain key fields are matching. i.e. If S.CAD_NAME, lngStoreNumber are a match to what's in the Main table. While
Temp table:
lngStoreNumber - CAD_NAME - lngcomponentSerial
1 - "CHK" - a
1 - "STK" - a
2 - "CHK" - a
Main table
lngStoreNumber - CAD_NAME - lngcomponentSerial
1 - "CHK" - a - LEAVE (EXISTS In Both Tables)
1 - "CHK" - b - DELETE (lngStoreNumber & CAD_NAME composite Found /lngcomponentSerial NOT Found in Temp)
1 - "STK" - a - LEAVE (EXISTS In Both Tables)
1 - "RMM" - a - LEAVE (lngStoreNumber & CAD_NAME NOT Found in Temp)
2 - "STK" - a - LEAVE (lngStoreNumber & CAD_NAME NOT Found in Temp)
2 - "CHK" - b - DELETE (lngStoreNumber & CAD_NAME composite Found/lngcomponentSerial NOT Found in Temp)
3 - "CHK" - a - LEAVE (lngStoreNumber = 3 Not in Temp table Subset)
Rule: Only delete the records for a particular CAD_NAME and lngStoreNumber from the Main table leaving all other CAD_NAME/lngStoreNumbers.
I'm running these updates in batches of lngStoreNumber. So the Temp table will only contain subsets of what's to be deleted from the Main table thus the need to link on the key fields only NOT to delete a Subset of lngStoreNumber/CAD_NAME. I think I've tried every possible query that doesn't work.
Here is query #1 to insert records missing from the Main table that exist in the Temp table. I think what I need is a variation of this???
SELECT D.*
FROM Main AS S RIGHT JOIN Temp AS D ON (S.CAD_NAME=D.CAD_NAME) AND (S.lngcomponentSerial=D.lngcomponentSerial) AND (S.lngStoreNumber=D.lngStoreNumber)
WHERE S.lngcomponentSerial is null AND S.CAD_NAME is null AND S.lngStoreNumber is null;
THANKS.
Hi
I have a small database with 4 tables that I am using for the current problem.
The tables are call, parents, mailman, orders.
Call and parents are related by the call ID (a primary key in the Call table.).
Mailman and orders are related by a Unique Id (a primary key in the mailman table.).
Forms involved are frmmain and frmsub.
Frmmain contains the call table information in the main form and parents information in the subform.
When a user enters a call with call ID and enters the operator name and parents information in the sub form,
When a user clicks the OK button on the main form, necessary changes should take place
if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.
Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information.
Orderid being autonumber.
Uniqueid from the mailman table.
Orderdate system date.
Ordertype should be “Mailman”
I'm using an UPDATE query to update records in one table (tblMain) from another table (tblTemp)
Here is my SQL :
Quote:
UPDATE [tblMain]
INNER JOIN [tblTemp] ON [tblMain].[MainField1] = [tblTemp].[TempField1]
SET [tblMain].[MainField2] = [tblTemp].[TempField2];
I only want to update the records in tblMain which have a corresponding record in tblTemp (linked by MainField1 / TempField1)
If any record doesn't appear in tblTemp, I want tblMain to retain the existing value for that record.
However, it appears that in such situations, the record in tblMain has it's MainField2 value set to null / ZLS.
I've tried using LEFT JOIN and RIGHT JOIN and also tried WHERE clauses but the result is the same every time.
MS Access 2013: I have two database tables as below:
tbl1_MainDB --- It has a field named as "City" where I get huge data for some city names. Sometimes This field may have some unknown/new names which are not listed in our 2nd table ("tbl2_RefrDB")
tbl2_RefrDB --- It's a reference table which has raw names for cities, and then standard names of their city and state in another fields.
Target --- I want to create a VBA prorgram (Sql query) which can look from tbl1_MainDB.[City] to tbl2_RefrDB.[Raw_City] field, and if found then pick the "Standard_State" and "Standard_City" record values from there, and update into the 1st table "tbl1_MainDB".
...if not found in "tbl2_RefrDB" table, then user can be informed & ask for updating the new/unmatched city record as a new record in this table.
Attached sample database for more details.
I have created a booking system for a set of resources for schools. Most schools have a membership which entitles them to 2 free sets. I have a booking form with a membership subform (membership table), and a booking details subform (kitloan table).
Once a school is selected on the main form, the membership subform shows the most recent record for that school based on schoolID.I want to display the number of sets they have already had within their membership period (can start at any time of the year, and lasts for 1 year) on the membership subform, so we know how many free ones they have left.
I therefore need to count the number of KitBkID (ID of the booking) in the Kitloan table where SchoolID = the SchoolID displayed on the membership subform, and the DateOut (booking date on kitloan table) is between the DateJoined and DateRenewal displayed on the membership subform (from membership table).
I can do this with a query which works when run and provided with the parameters SchoolID, DateJoined, and DateRenewal.
SELECT Count(Kitloan.KitBkID) AS CountOfKitBkID, Kitloan.SchoolID, Kitloan.DateOut
FROM Kitloan INNER JOIN Membership ON Kitloan.SchoolID = Membership.SCHOOLID
GROUP BY Kitloan.SchoolID, Kitloan.DateOut
HAVING (((Kitloan.SchoolID)=[Me].[SCHOOLID]) AND ((Kitloan.DateOut) Between [Me].[DateJoined] And [Me].[DateRenewal]));
What I can't do is get it to run on the form and take those values from the form.From the searching I've done, I'm thinking a DCount should be the way to go, but I cannot get the criteria right. I created a query (KitloanCountQry) so that criteria could come from both the kitloan and membership tables.
SELECT Kitloan.KitBkID, Kitloan.SchoolID, Membership.DateJoined, Membership.SCHOOLID, Kitloan.DateOut
FROM Kitloan INNER JOIN Membership ON Kitloan.SchoolID = Membership.SCHOOLID;
I have put the DCount as the control source for a textbox on the Membership subform (but have tried it in VBA too):
=DCount("KitBkID","KitloanCountQry")
This works but obviously gives me the total for all bookings.
[code]....
Although I have to admit to getting lost in the syntax. This produces #Error.