473,387 Members | 1,516 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

Overwrite Record if Value Exists

Curben
47
I have a form in my DB based on an updateable lookup query. When the form closes a macro runs removing all the records and updating them into a table bases an a yes/no control on the form. If the control is checked it will be added in a way that the next user will see it on their forms and queries. If the control is not checked the record is added in a way that it will not show up in other queries except on a report just to show it was reviewed.

What I am hoping to do is add a control to the form that checks if the initial value exists on the other table and if it already exists to overwrite the record (basically not advance to the next record when that condition is met and tab back to the first control in the same record)

This is access 2003 withe a 2000 DB
Oct 26 '07 #1
4 4346
nico5038
3,080 Expert 2GB
Your description is a bit vague. I have a hard time to grasp the intention of this update/delete/insert with checkbox and other control.

Why "move" records from one table to another ?

Nic;o)
Oct 27 '07 #2
Curben
47
Your description is a bit vague. I have a hard time to grasp the intention of this update/delete/insert with checkbox and other control.

Why "move" records from one table to another ?

Nic;o)
Main reason is the lookup that is done with our parts lookup and I wish to save the data as it was the day the information was input. Our parts data changes and is updated weekly.
Oct 30 '07 #3
nico5038
3,080 Expert 2GB
OK, then I would use a listbox or datasheet subform to display the parts.
Add a button to allow the user to select the current row (datasheet) or selected item (listbox) and use:
Expand|Select|Wrap|Line Numbers
  1. Me.FieldX = Me.subformname.form.FieldX 'For datasheet subform
  2. ' or
  3. Me.FieldX = Me.listbox.column(0) 'For listbox when in first (!) column
  4.  
When moving to another record, the FieldX field (bound to a field in a table in your mainform's recordset) will be saved.

Getting the idea ?

Nic;o)
Oct 30 '07 #4
Curben
47
OK, then I would use a listbox or datasheet subform to display the parts.
Add a button to allow the user to select the current row (datasheet) or selected item (listbox) and use:
Expand|Select|Wrap|Line Numbers
  1. Me.FieldX = Me.subformname.form.FieldX 'For datasheet subform
  2. ' or
  3. Me.FieldX = Me.listbox.column(0) 'For listbox when in first (!) column
  4.  
When moving to another record, the FieldX field (bound to a field in a table in your mainform's recordset) will be saved.

Getting the idea ?

Nic;o)
I will file it away as i may have use for it later. I refined a few other parts and created a delete query to run before the updates

Expand|Select|Wrap|Line Numbers
  1. DELETE tblInitialInput.*, qryEntryAudit.Status
  2. FROM tblInitialInput LEFT JOIN qryEntryAudit ON tblInitialInput.[Part ID] = qryEntryAudit.[Part ID]
  3. WHERE (((qryEntryAudit.Status)<>""));
and that seems to be having the desired effect as well
Oct 31 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Gord | last post by:
Hello, If you set the flag for an overwrite prompt using the 'Save' common dialog, how do you read the response when the user clicks the Yes or No in the 'overwrite' message box? Everything...
3
by: David Baumgarten | last post by:
I am trying to download a file from a ftp server and if the file already exists to overwrite it. Here is my code: FtpGetFile(hConnection, "1.pdf", FLocation & "\Temp\" & "1.pdf", False, 1, 0,...
1
by: Someonekicked | last post by:
I have a binary file, and I need to delete a specific number of characters in it. The file contains records, each record has a specific length. So the way I wanna handle deleting is that I will...
3
by: Brandon M | last post by:
I'm trying to get an Update Query to overwrite any records that already exist. By default it appears to skip any records in which the key already exists. Is there any way to change this?
5
by: BerkshireGuy | last post by:
Hello everyone, I have a bond form that a user uses to enter data. One of my fields, is PolicyNumber. I added some code on the Before Update event of txtPolicyNumber that checks to see if...
20
by: Bryan | last post by:
hello all... im trying to add a record to an sql db on ms sql server 2000, using vb.net. seems to be working.. except for one thing, one of the columns in the database is a bit datatype, and...
2
by: B-Dog | last post by:
Is there a way to make vb.net to overwrite the file when moving? Here is what I'm trying to do: If System.IO.File.Exists(dest) Then 'handle overwrite here If MessageBox.Show("Do you want...
5
by: Ben Sizer | last post by:
I need to copy directories from one place to another, but it needs to overwrite individual files and directories rather than just exiting if a destination file already exists. Previous suggestions...
8
by: Joe Duchtel | last post by:
Hello - I have the following code to detemine a file name when my application is saving a file. The problem is that if the file already exists and I select the Yes button in the "Do you want to...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.