Refresh Works For Existing Records But Not New Records

Nov 26, 2005

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

View Replies


ADVERTISEMENT

Refresh Table Records

Jan 29, 2007

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

View 4 Replies View Related

Import Records To Existing Table

Jun 9, 2006

Access 2000

This has to be an easy one but I can't seem to figure it out. I created a table by importing the structure from a .dbf file. Now I simply want to import other .dbf records from a bunch of .dbf files that have the exact same structure. How do Import these records into my existing table?

This should be a no brainer but I have spent an hour or so searching the help file and the internet.:mad:

View 5 Replies View Related

Copy Existing Fields To New Records

Dec 12, 2006

I have a database and I wish to be able to copy 10 out of about 20 fields into a new record automatically using a command button on a form. Similar to the way the Duplicate button or the Create New button would work but only with these selected fields. None of the fields I wish to duplicate are the primary key. The primary key is an AutoNumber field. After clicking the Save button, the primary key would increment one to confirm the save record.

Any help or suggestion would be appreciated!

Thanks!

View 3 Replies View Related

REFRESH Combo Boxes For Records On Continous Form

Jul 7, 2005

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

View 7 Replies View Related

Forms :: Refresh Current Form With Filtered Records

Sep 13, 2013

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.

View 4 Replies View Related

Updating Odd Fields In Existing Records From Excel

Dec 10, 2007

I currently have a database that is missing various pieces of information, we are getting a temp in to complete this. I don't want the temp adding data directly into Access so I have exported the table that needs updating into Excel.

How can I then import the new data without altering current data.

I know this is no problem for new records, but if I have a current record with a few fields of information missing how can I just import information into the blank fields. This can't be a manual process as there are hundreds of records.

Any comments would be much appreciated!

Chris

View 1 Replies View Related

Open A New Record Without Showing The Existing Records. THank You

Jun 27, 2006

Hi all,

How to open a fresh form without showing the existing records in the DB.


I.E I've 5 records in the DB. When I click on the add new form command button

Code: DoCmd.GoToRecord , , acNewRec

I want to have a fresh piece of form + I dont want the records which is in the DB to be shown.

Is that possible.

Thank You.

View 1 Replies View Related

Modules & VBA :: Importing New Records Into Existing Database

Jan 21, 2014

I have table1 in my (Access 2010) database that has exising records. I have another table2 that after I run a query, it first deletes the data in table 2, then imports new records into that table. I run the import into table 2 on a semi regular basis but have yet to copy those records into table 1.

With that said, using either a query or VB, how do I copy all the (new) records from table 2 into table 1 without altering any records that already exist in table1?

BTW, Table 1 has a main form with a subform within it

View 5 Replies View Related

Tables :: Adding Records To Existing Table

May 19, 2014

What I have is a database that I have done some tweaking on and in the meantime the original db has been in use which has added around 200 or so more records in the table.

What I would like to do is to just update the db that I have been working on with the older db table(the one who has the additional 200 records).

EX. DB A(Old DB, Newer Table) DB B(New DB, Older Table)

I want to put DB A table into DB B

Is this a simple fix? Or do I need to write some sort of query to update the records in the old table? I've tried to export the excel file and then import but it puts it in unrelated objects and then my switchboard or nothing works.

View 9 Replies View Related

Forms :: View Records Automatically Without Pressing Refresh Button?

Nov 20, 2014

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 Related

Tables :: Updating Blank Fields For Existing Records

Oct 9, 2014

I am in the process of building Append Queries for new records, and I know I can do an Update Query to enter specific information. However, how can I update multiple records from a (externally sourced) linked table to fill in blanks of an existing table? I have created a query to identify records with 1 or more matching criteria which contain the blank fields. I now want to update those blank fields. The data in each blank field is different for each record (same type, just different data).

View 1 Replies View Related

Modules & VBA :: Alert About Existing Records During Data Entry?

Jul 23, 2014

I need to alert the user of the database in case he/she enters a record that already exists in the database. If a person enters a key type and a serial number combination that already exists in the system and has status "issued", I need a pop up message to show up.

Am I missing some quotation marks somewhere in that DCount?

Private Sub SerialNumber_AfterUpdate()
If DCount("*", "tblIssuedKeys", "KeyType = '" & Me.KeyType & "' And "Status = 'Issued'" And SerialNumber = '" & Me.SerialNumber & "'") > 0 Then
MsgBox "This key has already been issued"
Cancel = True
End If
End Sub

View 2 Replies View Related

General :: Changing Existing Database From Overall To Monthly Records

Nov 8, 2013

I'm altering a database to have certain fields be recorded monthly.For example: instead of a client's file having "Total X Purchased" it would now be "Total X Purchased - Jan", "Total X Purchaed - Feb".

I want to spin the monthlies out of the master clients table & in to ex. tbl_clientsJan, tbl_clientsFeb, etc. but still have them linked; & have a "Totals" table that aggregates data from all of the tables (adds them up).

I have an existing form, & I was thinking of just creating tabs for the months & subforms in each with their sources as the month tables, & removing the fields that don't change month-to-month (e.g. client name), with relationships between them. I would start by copying all of the master table data in to the month tables & allowing edits from there.

the company doesn't track when the transactions occurred; I'm unable to group them along those lines.I'm new to Access & don't want to make a mistake.

View 9 Replies View Related

Queries :: Updating / Adding New Records To Existing Tables

Jul 29, 2014

I have a table with more than 60 fields and need to update it with records from another Access file with a table with an identical data structure.

Is it better to run an update query or an append query (i would have to delete the original records in the target table first) or a union query?

If I run an update query I will have to manually add each field to the query.

The update query will not add 'new' records. If I run an append it is quicker because I can use the * to match all fields, but i will have to delete the 'old' records first, as both tables use autonumber for the PK so the PK ID will be the same in each table (will get a key violation error).

If I import the 'new' table and run a union query it will match the fields and add the new records, but then i will have to create new table from that query.

View 1 Replies View Related

Tables :: Added New Field With Default Value Of Zero - How To Update Existing Records

Oct 3, 2012

I have an existing table that has calculations. When I added a new field with an default value of zero, it did not populate the existing records. Now my tables are not calculating. How can I update the existing records with the new default of zero.

View 2 Replies View Related

Tables :: Copy And Replace Existing Records Function In Access?

Dec 3, 2012

Is there a copy and replace existing records function in access?

I.e. I would want to copy records from one table to another (with same structure) and replace similar records with in original table in the new records (which have minor ammendments made).

View 3 Replies View Related

Forms :: Count Number Of Existing Records While Feeding Data

Jun 23, 2015

Table name :Imports
Field1 : ContainerNumber
Field2 : Bkg_number
Field3 : Size
Field4 : Weight

I have created a form which is having the above table as "control source". When data entering, if select a booking number from a Combo box, should checking how many records already having with same booking number and reflect the number of existing records in the form. If null records, it should show as "ZERO" records.

View 7 Replies View Related

Queries :: Insert Into - Copy Records From Another Base Into Existing Table

Apr 15, 2014

I'm trying to copy records from another base into existing table in current base by:

Code:
DoCmd.RunSQL ("INSERT INTO pivot (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) SELECT (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) FROM svod IN 'Z:NPSNPS - Operator - 1.accdb' ")

But it doesn't run. Says insert into syntax error.

View 6 Replies View Related

Queries :: List Of Dates And Records With No Matching Record OR Existing Record With Higher Date

Nov 24, 2014

I've been asked to get some information from my database and I'm a bit stuck.

I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field

My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.

So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)

I can manage a query that looks at a certain date that it prompts for on each run:

Code:
SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded
FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK
WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));

I would like a query that lists all dates in a range, and shows the same information for each day listed.

View 9 Replies View Related

Records Existing In Main Table Not Found In Temp Table

Apr 11, 2007

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.

View 2 Replies View Related

Modules & VBA :: Possible To Export Select Records And Fields In Those Records To A Specific Location?

Jun 15, 2013

In an Access 2010 form is it possible to export select records and fields in those records to a specific location?

Code:
Set objDialog = Application.FileDialog(4)
With objDialog
.AllowMultiSelect = False
.Title = "Please select a File"
.InitialFilename = "C:"
.Show
If .SelectedItems.Count = 0 Then
MsgBox ("Action Cancelled")
Else

[code]....

The user can select the directory using the code above, but can specific fields in records be exported to a excel workbook in that selected directory?For example, if the are 5 records in the database can the fields LastName,FirstName,BirthDate in records 1,2,3 be exported to Setup.xlsx in that selected directory?

View 1 Replies View Related

Tables :: Linking Records In One Table To Multiple Records In Another And Assign Percentage?

Nov 21, 2012

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.

View 3 Replies View Related

Queries :: Mass Duplicate Main Records And Related Subform Records

May 29, 2014

In my simple database (attached), I need to mass duplicate Tasks and their Notes.

I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.

The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.

I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?

Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.

View 5 Replies View Related

Forms :: Getting Records On Start And End Date / Error - No Records Found

Jun 27, 2013

I am trying to get the records on start and end date, still showing error no records found.

My code is like this:

Private Sub Command90_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strReport As String
Const strcJetDate = "#dd/MM/yyyy#"

[code]...

View 1 Replies View Related

Forms :: Limited Number Of Records In Continuous Form / But Now Can't Add Records

Mar 22, 2013

One shows my form with the Transporters Subform with 3 entries, and 1 entry.The three line items that say "Transporter" are in one subform. I used this code

Code:
Private Sub Form_Current()
If Me.RecordsetClone.RecordCount >= 3 Then
Me.AllowAdditions = False
End If
End Sub

to limit the number of records I can add to 3 or less.My issue is that I lost the blank text box that allows you to add another record. So, if I only have one Transporter listed, there's no box to let me add a second or third.I have the following properties for the Transporters Subform set to "Yes":

Data Entry
Allow Additions
Allow Deletions
Allow Edits
Allow Filters

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved