Software: Access 2003
Objective: I have two tables in my database. I want to be able to execute code in a button on a form that verifies that a record in table2 exists via user input with 'InputBox'. If the record doesn't exist, then I would like to have the record copied from table1 to table2.
What I have currently done: I currently use 'FindFirst' along with 'InputBox' to verify if a record exists in a table, however, I am unable to figure out the code to switch to another table and check if the record exists there.
Additionally, I don't know how to copy specified fields from one table to another.
If any of this causes any confusion then let me know and I'll be happy to try and better explain.
6 9451
The first question that needs to be asked is why you would want identical records in two different tables? This pretty much violates concept of a relational database! Perhaps if you could provide us with more comprehensive explanation of what you're trying to do we could provide some guidance.
Welcome to The Scripts!
Linq ;0)> NeoPa 32,556
Expert Mod 16PB
Each recordset in your code is simply stored in a different variable. Set a recordset up for each table you need to access.
To add a record you can either use a SQL command or the {recordset variable}.AddNew() method.
NB. Unless you're doing some sort of logging process, linking to data rather than simply copying the data across is generally the preferred way of providing access to it (See Linq's post).
I understand what you are saying about linking the data but this particular situation is rather unique in which the data in table1 is erased and updated every 24 hours and table2 keeps occasional records from table1. This is why I need to verify if a particular record exists in table2 and if it doesn't, copy the record over from table1.
The first question that needs to be asked is why you would want identical records in two different tables? This pretty much violates concept of a relational database! Perhaps if you could provide us with more comprehensive explanation of what you're trying to do we could provide some guidance.
Welcome to The Scripts!
Linq ;0)>
Software: Access 2003
Objective: I have two tables in my database. I want to be able to execute code in a button on a form that verifies that a record in table2 exists via user input with 'InputBox'. If the record doesn't exist, then I would like to have the record copied from table1 to table2.
What I have currently done: I currently use 'FindFirst' along with 'InputBox' to verify if a record exists in a table, however, I am unable to figure out the code to switch to another table and check if the record exists there.
Additionally, I don't know how to copy specified fields from one table to another.
If any of this causes any confusion then let me know and I'll be happy to try and better explain.
Here's some code based on an Employees2 Table, which would parallel your Table2 scenario. This code should do everything that you had requested in your Post. I'll just Post the code for now, since it is getting past my bedtime, but should you have any questions whatsoever, please feel free to list them, and either myself or one of the other fine gentlemen (yes NeoPa, you too! (LOL)), will be happy to assist you. -
Dim MyDB As DAO.Database
-
Dim MyRS As DAO.Recordset
-
Dim varLastName As Variant
-
Dim strMsg As String
-
Dim intResponse As Integer
-
-
Set MyDB = CurrentDb
-
Set MyRS = MyDB.OpenRecordset("Employees2", dbOpenDynaset)
-
-
'varLastName = Trim(InputBox("Enter a Last Name to Search For."))
-
'OR
-
'Forgive my confusion, but why not simply read the value from an
-
'existing Text Box on the Form
-
varLastName = Me![txtLastName]
-
-
If Len(varLastName) = 0 Then Exit Sub
-
-
With MyRS
-
If .RecordCount > 0 Then
-
'Traverse the Recordset to populate it, if it contains Records
-
.MoveLast
-
End If
-
.FindFirst "[LastName]='" & varLastName & "'"
-
If .NoMatch Then
-
strMsg = "No records found with a Last Name of " & varLastName & _
-
". Do you wish to Add " & varLastName & " to the Database?"
-
intResponse = MsgBox(strMsg, vbQuestion + vbYesNo + vbDefaultButton1, "Add Record")
-
If intResponse = vbYes Then
-
.AddNew
-
![LastName] = Me![txtLastName]
-
![FirstName] = Me![txtFirstName]
-
![EmployeeID] = Me![txtEmployeeID]
-
.Update
-
End If
-
Else
-
MsgBox varLastName & " already exists in Table Employees2."
-
End If
-
End With
-
-
MyRS.Close
-
Set MyRS = Nothing
I really appreciate your post, it helped quite a bit. Is there an easier way to copy a complete record from one table to another? In the example you gave, you copied 3 fields from the record and in my example I have well over 3 fields that would need to be copied and it would require quite a bit of code to copy all of the fields in the record. Is there a function that will copy a complete record instead of needing to copy the record one field at a time?
NeoPa 32,556
Expert Mod 16PB
There should be a .Fields() collection for each recordset. You could loop through them all transferring each value across by index or even by name if preferred.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Drew |
last post by:
I have a db table like the following,
UID, int auto-increment
RegNo
Person
Relation
YearsKnown
Now here is some sample data from this table,
|
by: news.hp.com |
last post by:
I have situation where I need to copy multiple records (only certain fields)
from a Rules table to an Events table based on a selection identified in a
combo box. When the selection is made in a...
|
by: dskillingstad |
last post by:
I've been struggling with this problem for some time and have tried
multiple solutions with no luck.
Let me start with, I'm a novice at Access and I'm not looking for
someones help to design my...
|
by: david |
last post by:
Hi,
I've been reading tons of posts on how to copy records, but to no
avail....i'm still stuck.
There are three tables: Main, Sub-Form1 & Sub-Form2
I have a form which displays some data....
|
by: Sourcerer |
last post by:
Hi all.
Can I do this to duplicate the contents of struct a in struct b
struct myStruct a, b, *aptr, *bptr;
aptr = a;
bptr = b;
Do something to initialize contents of structure a...
|
by: Shizbart |
last post by:
MS Access 97
Beginner/Moderate Level User
I am trying to create a Database to track Workouts in MS Access 97.
I have one Table named Workouts that contains the following Fields: Workout...
|
by: colleen1980 |
last post by:
Hi:
Can any one please help me when user select 2 dates from DDLDate1
10/09/2006 and DDLDate2 10/12/06 and the name and it close the form. I
need to create multiple records in the another table on...
|
by: bonneylake |
last post by:
Hey Everyone,
Well recently i been inserting multiple fields for a section in my form called "serial". Well now i am trying to insert multiple fields for the not only the serial section but also...
|
by: bonneylake |
last post by:
Hey Everyone,
Well i am not sure if this is more of a coldfusion problem or a javscript problem. So if i asked my question in the wrong section let me know an all move it to the correct place.
...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |