Saving Record To Run Query & Update

Jun 14, 2005

Hi all,

I've got this form working ok but need to add some extra functionality but haven't a clue how to do it!

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=10175

The uploaded database has two main forms. The first is used to enter customer data, the second runs from a query and shows all those records that have been completed in the first form (frmCustInfo - AgentLog = Yes) but not completed in the second form (frmControlsSource - AnalystLog = No)

Then more data is input into one of the subforms (frmControlChecks). Now in order for me to see the results of the queries connected to two other subforms I have to move off the record and back to it (for the record to be saved and then the queries run)

After moving back to the record, the query results on the right (Information Only) are manually added to the last subform 'Input Two', then the user would move on to the next record and do the same.
-------------------------------------------------------------------------------------------------------------
What I need it to do then is this: When the user tabs off the last field in the subform (frmControlChecks), the queries to the right (two subforms) would run, showing the results and also populating the last subform (frmAnalystInput)

The user would then click AnalystLog and move to the next record.

I hope this is possible without redoing the forms/queries because it's taken ages to get this far

I've also added info in this sample database and some working data if you want to take a look...thanks for any help

View Replies


ADVERTISEMENT

Saving Value To Variable? (update/make Table Query)

Aug 28, 2007

I am trying to create a table to show the parent/child relationships between assemblies, sub-assemblies and detail parts. The table I start with has 2 fields a "Part Number" and an "Indenture".

Please forgive all the periods, couldnt think of how else to seperate the values to depict the tables.

Example:
IND..PN
00....A
01....B
01....C
02....D
03....E
03....F
01....G
00....H
01....I
02....J

I can get the first step to completeing the new table of parent/child relationship like this:

PN....00....01....02....03
.A.....A
.B....."".....B
C......"".....C
D......"".....""......D
E......"".....""......"".....E
F......"".....""......"".....F
G......"".....""......""....G
H......H
I......""......I
J......"".....""......J

Where "" are null values

My problem is making it so that every part number has its own array, aka [family tree], so that for part "F" the table would hold "A,C,D,F"

I though an update query where for example:
*"Zero" is a single field string.

IIF([00]<>null(), Save [00] to Zero, Write Zero to [00])

Would work but have been unable to find any information about passing a field value to a variable for later use or comparison. I would have already done this in excel if the table had less that 65000 records.


Example of final table:

PN....00....01....02....03
.A.....A
.B.....A.....B
C......A.....C
D......A.....C......D
E......A.....C......D.....E
F......A.....C......D.....F
G......A.....C......D....G
H......H
I.......H......I
J.......H......I......J

Any help is greatly appreciated.
BsK

View 1 Replies View Related

General :: Saving Data - Update Or Cancel Update Without AddNew Or Edit

Apr 13, 2013

My membership database has worked fine until recently. Now I cannot save inserted data. On attempted saving "Update or CancelUpdate without AddNew or Edit" appears.

The problem. relates to 2 tables Member and Addresses. PK in the parent table Member is ID. In the Addresses Table the FK is ID. There is a One to One relation between the tables and Referential Integrity is set. I know 1 to 1 is not good but it worked fine in this small database.

Browsing the all of the existing records is fine.

View 9 Replies View Related

Modules & VBA :: Running Update Query On Record And Have Form Show Updated Record

Jan 26, 2015

i want to be able to create an On Click Event when pushing a command button that will run an Update query to update a record and after it has been updated that specific record will pop up on a Form and be displayed. i know a different way is to run the Update query and then have it displayed in a Select query but i want it to be displayed on a Form instead. is it possible?

View 4 Replies View Related

Forms :: Saving New Record When Moving To Another Record In Same Subform

Jan 17, 2014

I have a subform with records of call details (date, time, subject) - the date and time are autogenerated and subject is a text field. After entering text in the subject field and then clicking on the close button in the main form, or clicking anywhere else in the main form, the record is saved to the table. However, if I enter text in the subject field and then move up to view previous records (within the subform) and then click the close button in the main form the record is not saved in the table. How can I either save the record before allowing the user to move to another record within the subform or before exiting the subform set the focus to the new record so that it will be saved on exit?

View 4 Replies View Related

General :: Update Record ID To Another Record ID In Same Table And Update Related Records

Aug 22, 2013

I have a table called tblCompanies. When a company acquires another company, I need a method by which the acquired company's CompanyID (PK) can be updated to the new company's CompanyID (PK). I also need to be able to update all related CompanyIDs (FKs) to the new value in related tables.

In cases in which the new company does not have an existing record, there is no problem: the company name simply gets changed to the new company and the existing CompanyID is maintained. I then use an audit table and Track Changes function to keep track of the company name data and a union query to keep the old names in the selection lists.

The problem is when both companies already have existing records in the table.

So, let's say I have records for Company A and Company B. Company A merges with Company B and Company B is now the main record. What is the best, simplest and easiest way to update the CompanyID (PK) from A to B and change the CompanyID (FK) to the new value in all related tables?

I am envisioning a pop-up form that directs the user to select the new company and then an update query happens behind the scenes... but exactly how does the criteria for the update query get selected and how do all the related tables get updated? My vba skills are pretty basic, will I need extensive coding to do something like this?

View 6 Replies View Related

Update A Record Without A Query?

Oct 31, 2007

Hi,

Don't know if this is possible, or if im being just plain stoopid:

Is there a vb command that will update a record without having to use a query?

My problem is this - I have an HR database which has allows us to add employees that are going to start. We then set their [Activity Status] from "Starting" to "Active". At the moment this is done manually, but what i would like to do is have this automatically change when the [Start Date] = Date().

The code i tried to use (but is obviously wrong) is:

If [Activity Status] = "starting" And [Start Date] < Date Then
Set [Activity Status] = "Active"
End Sub

Any thoughts what i might change "Set" to, to make this work?

Thanks,

Ferg.

View 6 Replies View Related

New Record On Saving

Apr 5, 2006

Hello guys

I have one small quick question. How can get blank or new record after saving

Cheers

View 3 Replies View Related

Saving A Record AS A NEW RECORD From A Form

Nov 23, 2006

Iam very new to ACCESS. So please do forgive me for
believing that the "SAVE AS" function in ACCESS is as easy as
something similar we do in Microsoft Word.

After searching this forum and others I realise that there is no easy way
and it is considered as some thing for serious users.
(with additional coding not available in ACCESS)
Perhaps there are easier ways that I am not aware of.

I would like to open a record in a FORM which has one Combo Box
for text data, several text and Numeric fields.
I then need to change a few items and save the record as a New
Record
Only a few (mostly numeric fields) and some date entries will be changed
as well as a couple of calculated fields in the NEW RECORD.

I shall appreciate any suggestion as to how to do this.

View 13 Replies View Related

Can't Update Record From Select Query

Jul 30, 2006

Hi,

I have Access 2002 on Windows XP.

The last version of Access I've used was 97 but I'm getting back into it. I've read a couple of things that recommend creating a form based on a query, not a table, especially if a calculated field is involved.

When I create a select query based on 1 table, I can change/add/delete records right in the results of the select query, which will carry over to the form just fine.

However, when I use an additional table and join them in my select query, I can no longer update any of the fields that show in the query result. The link I'm using is just a 1 to 1.

How can I get around this? I'm using the second table just for lookup purposes (use the value of one of the fields in a calculation), but I want to be able to update the fields from table 1 from the form.

Thanks.

View 3 Replies View Related

Using Update Query To Change Record Value

Sep 12, 2007

I have a list of codes that need to be changed if it is part of the a list that need to be updated after being entered into the database. I created a function that holds the old values and what they need to be updated to. To get this accomplished on a form do I just need to write a Update query and then reference the query to the appropriate field I am trying to update with new codes for the 11 codes needing to be changed and leaving the other codes the same.

View 1 Replies View Related

Queries :: Can't Get Query To Even ALLOW To Update Record

Jul 1, 2015

I can do this in like 5 seconds in SQL Server but I can't get the query to even ALLOW me to update records.

Table1 - Columns
Query1 - ColumnDesc

Code:
SELECT DISTINCT C.Column
FROM Columns C INNER JOIN (
SELECT Cols.Column, Count(Cols.DataType)
FROM (SELECT DISTINCT Columns.Column, Columns.DataType FROM Columns) Cols
WHERE Cols.DataType = 'char' OR Cols.DataType = 'varchar'
GROUP BY Cols.Column
HAVING Count(Cols.DataType) > 1
) C2 ON C.Column = C2.Column;

Simple, straight forward query, that grabs all the rows from table Columns where there are more than one DataType per Column [name], and either one of those datatype strings are 'char' or 'varchar'.Now I want to UPDATE table Columns to set all of the columns whose "column" value is in the above query, and set all those DataType values to 'VarChar'. Thus I run that UPDATE query, and the above query should come back empty afterwords in SQL Server I would simply write:

Code:
UPDATE Columns
SET DataType = 'varchar'
WHERE EXISTS (
SELECT 1 FROM ColumnDesc INNER JOIN Columns ON ColumnDesc.COlumn = Columns.Column
)

And this would already have been done.But Access doesn't like that syntax. using the designer it created sql like this:

Code:
UPDATE Columns INNER JOIN ColumnDesc ON Columns.Column = ColumnDesc.Column
SET Columns.DataType = 'varchar'

But that wasn't an "Updateable" query.I have to run this on several patterns and right now I'm completely screwed if I can't get this to work.

My end goal is to have:
SELECT DISTINCT Columns.Column FROM Columns
return the same # of rows as
SELECT DISTINCT Columns.Column, Columns.DataType FROM Columns

for each duplication I will have to do different algorithm, but I can't even get one update query to work so I'm currently frustrated (and ready to reaffirm my belief that Access should have been discontinued 5 versions ago).

View 1 Replies View Related

Saving A Record On Lost_focus

Jun 26, 2006

I'm terribly sorry to bother on what seems like a very simple matter, but I've just given up on MicroSofts documentation.

I just want to execute the Shift+enter type of save-record command (also available from the "Records" menu at the top) when a field loses focus. This would need to be part of my VB code as I'm doing other things on this event as well.

Any help is greatly appreciated.

View 1 Replies View Related

Saving Record From Form

Feb 23, 2005

Hello,
I have a form and I have a button so people can open records to load into the form. The problem is that whenever they close the form it updates the information in the table. How do I stop that from happening?
I want the information in the table to update only when they hit the button I made (which works).

And how do I stop it from loading the record when I open the form, or maybe I can specify to load a blank record instead, how do I do that?

Thanks in advance!

View 1 Replies View Related

Vb Code For Saving A Record

Feb 22, 2006

Hi,

This should be an easy one...I would like to save a record part-way while filling out a form. I know this can be accomplished by pressing Shift+Enter, but does anyone know the vb code for saving a record while working on a form?

Thanks.

View 1 Replies View Related

Record Saving Woes

Mar 16, 2006

Ok, here is my scenario.

I have several FE's each saving in their own BE. They are all the same but for different areas of our business. Each has around 20-30 users. I recently introduced another form into the FE that has linked tables to another different BE in another folder. Each FE out there with its own BE now has this form and all of the new forms save to the same (one) BE. Ok, got it?

Now, sure its possible to have them all together and I would anticipate someone suggesting this. Its split up to keep sizes down and performance up among other reasons.

In any event, here I am. All of the new form introductions went smoothly without a hitch. The last one however is causing trouble. On submit an e-mail is sent, the user is asked if they want to submit another form, and if yes a new record is created. If no the form is closed.

E-mails are always sent, no problem. The trouble is saving the record. All except one of the different FE's I have out there in different folders save the record into the table just fine. There is one though that does not do this. The mail is sent and the program behaves normally with no errors but the record is not saved. This does not happen to all users of this FE either. There are some users that work fine and the record is saved in the BE yet other users do not save the record. You can see the .ldb appear and disappear. Our IT dept has been through the permissions time and time again and tell me they are ok. The FE and main BE are in one folder while the BE that the new form talks to is in its own folder. Remember, this works fine for several other instances, its just the one that doesn’t save the record.

I did have a chance to see the permissions the other day and it seems a mess to me. They have users in individually, as they were set up initially and the same users are also in there in a group and maybe 2 groups. The permissions look a mess to me… they really do…. but that doesn’t necessarily mean they would not work and can be blamed. I have no control over this part and it drives me nuts!

What I have done...... copied a working FE from another area into the troubled folder. No good. I have removed the new form along with its linked tables and the record is saved. Put the form and linked tables back in and it stops saving again. The code compiles just fine. I have done compact and repairs.

Any thoughts or ideas out there??? If you need more info ask. I understand what I wrote but you might not LOL

View 8 Replies View Related

Vba Code For Saving A Record

Apr 13, 2006

hola,

does anyone know the code to save a report with a changing file name?

here are the details.
i have a reoprt that changes weekly. i want to save a weekly file with the end result to be called "Downtime Cost Report #/##/##", where the date is pulled in from a qry called "qry_Actual_Costs_Thru" and field name [Fiscal Week].

i know it is something like:

DoCmd.Save acReport, "Downtime Cost Report"

Thanks
T

View 4 Replies View Related

First Row Is Blank When Saving A Record

Dec 9, 2012

Please find attached table

When saving a record first one is blank row how to solve. For the first time only it happened why? How to solve.

View 6 Replies View Related

Message Box For Saving Record?

Dec 28, 2011

i want to have message box asking "Want to save the record or not" to save the record.

form contain the subform also. i read some thread regarding that issue they suggest i have to use form's beforeupdate property. i used it for the main form but message box is appearing before entering the values in subform. i mean i have to enter some data in subform also.

i am attaching database for ur references. after entering PO nunber PO date and selecting client message box is apearing but it should be come after updating data in subform i.e. details.

View 5 Replies View Related

Update Query Doesn't Select 1 Record

Nov 22, 2006

Upon closing my frmInventory the amount stock of stock is checked against a minimal stock value. If the stock amount is below a set minimal value a subsequent form is opened telling you that stock is low and an email message is generated to notify a manager. I have a checkbox on that form which is set to "True" upon close using an update query. The checkbox is there to give users the option to either send or not send a reminder message that stock is low when a message has already been sent earlier.

The problem is that using that update query ALL records are set to "sent=true" and not just the 1 record I intend.

This is my code in the "on close" event:

DoCmd.OpenQuery "qryUpdateEmailMinimal_True

and here's the SQL:

UPDATE tblInventory SET tblInventory.emailSentMinimal = True;


I assume what is missing is a reference to an inventoryID number. How do I do that?

View 3 Replies View Related

Update Query Creating Extra Record

Jan 5, 2005

i have this problem that is bugging the crud out of me:
sql="UPDATE bedrifter SET pr=" & Request.Form("pr") & ",totalindexedpages=" & Request.Form("tip") & ",totalinboundlinks=" & Request.Form("til") & ",description='" & Request.Form("dsc") & "' WHERE created='" & Request.Form("ts") & "'"

conn.Open connStr
conn.Execute(sql)
conn.close()
Set conn = nothing

when i run this code it updates the correct record (line in my access db) but then it also adds a new line with only that info in the update query. why is it doing this? when i update using the ID instead of using the timestamp in the WHERE clause it works fine. really frustrated...

View 1 Replies View Related

Error In Update Query: Record Is Deleted

Jul 16, 2007

While executing this query, I get this error code:

Record is deleted.

UPDATE [MDL-10] SET [MDL-10].[File Path] = "Download#\192.168.4.40h driveNTPC SIPATDMSSDocuments" & [ProjectNo] & "" & [Client Drg No] & ".pdf#"
WHERE ((([MDL-10].[REV 00 SUBMISSION]) Is Not Null));

I tried all but could not find any reason. Please help

View 3 Replies View Related

Queries :: Update Query Not Working On Last Record

Jun 26, 2014

I have this update query that is triggered by an after update event on a main form. The record being updated are in a continuous subform. It works well except from the last added/modified record. If I save and close the form and then open it again it works for all records but if modify or add a record, the update query will not work for that last modified/added record.

I have tried several things such as save record, use dirty = false for the on exit event of the subform control but nothing works. Here is the procedure:

Code:
Private Sub cboPoCurrency_AfterUpdate()
On Error GoTo ErrHandler
Dim db As Database
Dim strSql As String
Dim lngID As Long
Dim dblRate As Double
Set db = CurrentDb

[Code] ....

View 14 Replies View Related

[B]How To Stop Access From Saving The Record When The[/B]

May 25, 2007

Hi

I need to know how to stop access from saving the record when the
subform query is on focus?


Thanks

View 5 Replies View Related

Combo Box Selection Not Saving Record

Sep 5, 2005

Good morning all! I've got three bound combos set to visible = false on their property sheetsThese will remain hidden or will be revealed depending on the selection made in another bound combo cmbType.

The select case below works fine on the form but the selection affects all records not just the current record. Open record 1 and select "compliment" from the first combo - the three others correctly stay hidden. Move to record 2 and select complaint and the combos appear - Great! Move back to record 1 and they're showing there as well. Groan. Helllllppp!


Private Sub cmbType_AfterUpdate()
On Error Resume Next

Select Case cmbType.Value
Case "Complaint"
cmb2Day.Visible = True
cmb15Day.Visible = True
cmbOver15.Visible = True

Case "Compliment"
cmb2Day.Visible = False
cmb15Day.Visible = False
cmbOver15.Visible = False

Case "Suggestion"
cmb2Day.Visible = False
cmb15Day.Visible = False
cmbOver15.Visible = False

End Select
End Sub

View 1 Replies View Related

Saving Record Without Access Prompt

Mar 13, 2006

I want to save a record with a command button. I am using RunCommand acCmdSaveRecord. Access gives me a prompt saying "You are about to update 1 record. Are you sure you want to update this record?"

How do I get it to stop prompting me every time?

View 1 Replies View Related







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