Form create record in 2 tables (1 Viewer)

[evictme]

Hello All,

I have a simple form with save / cancel buttons and I want to run an if statement (or something) on click of the save button that will save the entry into the table the form is used for but also create an entry into a different table that will copy certain fields into specific fields in the other table.

Form - Homevisits

Table 1: Homevisits COPY TO Table 2: ClientNotes
Field 1: Home visit date COPY TO Field 1: NotesDate
Field 2: Notes COPY TO Field 2: Notes
Field 3: User Modified COPY TO Field 3: User Modified
field 4: Date Modified COPY TO Field 4: Date Modified
Field 5: Time modified COPY TO Field 5: Time Modified
Field 6: ClientID COPY TO Field 6: ClientID


I would appreciate any direction here. Ive been looking all morning for something suitable but havent had any luck.

Thanks,

 

[theDBguy]

Hi. This is definitely possible but just wanted to say, in case no ever mentioned it before, it is really not recommended to store the same information in multiple locations/tables because it puts your data integrity at risks. If you update the data in one location, you will have to remember to update them also in the other locations. If you missed one, then you could be using outdated data. Is there any particular reason for wanting to do this? Just curious...

 

[evictme]

Users need to see when homevisits are completed for clients in the clientnotes. The person who was inputting the homevisits into the homevisits form had to go to the client form and manually enter a new note, with the same exact information, into clientnotes.

Its redundant. I can update the tables using an update query but there is no constant to the homevisits, neither in days they are inputted or time frame of the actual visits. They are entered at random internvals and of random dates.

I want it to be that once it is saved in the homevisits it will indicate something in client notes. it is important because notes are descending and timeframe matters for the users.

it doesnt matter if the person missed one, they can always go back and enter it later. What is most relevant is that the information appear in clientnotes when its entered at all.

 

[theDBguy]

Users need to see when homevisits are completed for clients in the clientnotes. The person who was inputting the homevisits into the homevisits form had to go to the client form and manually enter a new note, with the same exact information, into clientnotes.

Its redundant. I can update the tables using an update query but there is no constant to the homevisits, neither in days they are inputted or time frame of the actual visits. They are entered at random internvals and of random dates.

I want it to be that once it is saved in the homevisits it will indicate something in client notes. it is important because notes are descending and timeframe matters for the users.

it doesnt matter if the person missed one, they can always go back and enter it later. What is most relevant is that the information appear in clientnotes when its entered at all.

Hi. If you're saying both tables don't necessarily have the same exact notes all the time but only have similar notes sometimes, then you should be able to use an APPEND query as soon as the notes are entered by the user. Otherwise, if all the notes are always going to be the same, then I might advice against doing it this way. If so, you can either keep the notes in just one of the tables or create a separate table for it and just link the other two to the new notes table.

 

[evictme]

How would i do an append query on the cmdsave button click?

 

[theDBguy]

How would i do an append query on the cmdsave button click?

Maybe something like:

strSQL = "INSERT INTO TableName(FieldName) VALUES('" & Me.Notes & "')"
CurrentDb.Execute strSQL, dbFailOnError

 

[evictme]

one last request? whats the format of this code for adding multiple values to multiple fields?

I keep getting a syntax error and im sure its because im not coding it correctly.

 

[theDBguy]

one last request? whats the format of this code for adding multiple values to multiple fields?

I keep getting a syntax error and im sure its because im not coding it correctly.

Maybe try something like:

strSQL = "INSERT INTO TableName(Field1, Field2, Field3) VALUES(" _
    & Me.Value1 & "," & Me.Value2 & "," & Me.Value3 & ")"

 

[evictme]

Ive been trying the following:

strSQL = "INSERT INTO ClientNotes(Notes) VALUES('" & Me.Home_Visit_Notes & "')" And "INSERT INTO ClientNotes(NotesDate) VALUES('" & Me.Home_Visit_Date & "')" And "INSERT INTO ClientNotes(ClientID) VALUES('" & Me.ClientID & "')" And "INSERT INTO ClientNotes('User Updated') VALUES('" & Me.User_Updated & "')" And "INSERT INTO ClientNotes('Date Modified') VALUES('" & Me.[Date modified] & "')" And "INSERT INTO ClientNotes('Time Modified') VALUES('" & Me.[Time modified] & "')"
CurrentDb.Execute strSQL, dbFailOnError

 

[evictme]

Maybe try something like:

strSQL = "INSERT INTO TableName(Field1, Field2, Field3) VALUES(" _
    & Me.Value1 & "," & Me.Value2 & "," & Me.Value3 & ")"

Ive tried this and this is the error i get:

Run-Time error '3075'
Syntax error (missing operator) in query expression 'visit done-evf'.


That last part is something in the notes field for the current record.

 

[theDBguy]

Ive been trying the following:

strSQL = "INSERT INTO ClientNotes(Notes) VALUES('" & Me.Home_Visit_Notes & "')" And "INSERT INTO ClientNotes(NotesDate) VALUES('" & Me.Home_Visit_Date & "')" And "INSERT INTO ClientNotes(ClientID) VALUES('" & Me.ClientID & "')" And "INSERT INTO ClientNotes('User Updated') VALUES('" & Me.User_Updated & "')" And "INSERT INTO ClientNotes('Date Modified') VALUES('" & Me.[Date modified] & "')" And "INSERT INTO ClientNotes('Time Modified') VALUES('" & Me.[Time modified] & "')"
CurrentDb.Execute strSQL, dbFailOnError

Yeah, no, don't thing that would work. So, to verify, you want to insert data into only one table (ClientNotes), correct? If so, then you want to add data into multiple fields in that one table, correct? If so, then the syntax would be more something like:

"INSERT INTO ClientNotes(Notes, NotesDate, ClientID, " _
    & " [User Updated], [Date Modified], [Time Modified]) VALUES(" _
    & Me.Home_Visit_Notes & "',#" _
    & Format(Me.Home_Visit_Date, "yyyy-mm-dd") _
    & "#," & Me.ClientID... and so on... making sure you delimit the values correctly

 

[evictme]

Yeah, no, don't thing that would work. So, to verify, you want to insert data into only one table (ClientNotes), correct? If so, then you want to add data into multiple fields in that one table, correct? If so, then the syntax would be more something like:

"INSERT INTO ClientNotes(Notes, NotesDate, ClientID, " _
    & " [User Updated], [Date Modified], [Time Modified]) VALUES(" _
    & Me.Home_Visit_Notes & "',#" _
    & Format(Me.Home_Visit_Date, "yyyy-mm-dd") _
    & "#," & Me.ClientID... and so on... making sure you delimit the values correctly

Yes. When I click the save it should save it to CLientNotes,

I am using the format you're showing me but im just not getting it correctly. I dont know the formatting options for these fields very well nor how to correctly code it.

Im just not getting it.

 

[theDBguy]

Yes. When I click the save it should save it to CLientNotes,

I am using the format you're showing me but im just not getting it correctly. I dont know the formatting options for these fields very well nor how to correctly code it.

Im just not getting it.

Simply, if the field's data type is Text, you need to use a single quote for a delimiter. If the field's data type is Date/Time, then you need to use an octothorpe (#) as the delimiter. If it's a Number data type, you don't have to use any, like for ClientID.

 

[evictme]

Simply, if the field's data type is Text, you need to use a single quote for a delimiter. If the field's data type is Date/Time, then you need to use an octothorpe (#) as the delimiter. If it's a Number data type, you don't have to use any, like for ClientID.

Here is where i am at now:

strSQL = "INSERT INTO ClientNotes(Notes) VALUES('" & Format(Me.Home_Visit_Notes, "'") & "')" And "INSERT INTO ClientNotes(NotesDate) VALUES('" & Format(Me.Home_Visit_Date, "yyyy-mm-dd") & "#)" And "INSERT INTO ClientNotes(ClientID) VALUES('" & Me.ClientID & "')" And "INSERT INTO ClientNotes([User Updated]) VALUES('" & Format(Me.User_Updated, "'") & "')" And "INSERT INTO ClientNotes([Date Modified]) VALUES('" & Format(Me.[Date modified], "yyyy-mm-dd") & "#)" And "INSERT INTO ClientNotes('Time Modified') VALUES('" & Format(Me.[Time modified], "tttt") & "#)"

Would you check to see where I am missing the delimiter, I am still getting a data type mismatch error

 

[theDBguy]

Here is where i am at now:

strSQL = "INSERT INTO ClientNotes(Notes) VALUES('" & Format(Me.Home_Visit_Notes, "'") & "')" And "INSERT INTO ClientNotes(NotesDate) VALUES('" & Format(Me.Home_Visit_Date, "yyyy-mm-dd") & "#)" And "INSERT INTO ClientNotes(ClientID) VALUES('" & Me.ClientID & "')" And "INSERT INTO ClientNotes([User Updated]) VALUES('" & Format(Me.User_Updated, "'") & "')" And "INSERT INTO ClientNotes([Date Modified]) VALUES('" & Format(Me.[Date modified], "yyyy-mm-dd") & "#)" And "INSERT INTO ClientNotes('Time Modified') VALUES('" & Format(Me.[Time modified], "tttt") & "#)"

Would you check to see where I am missing the delimiter, I am still getting a data type mismatch error

Hi. Did you see what I posted earlier? I removed all your "ANDs" because that's not the correct syntax. When you want to update multiple fields in the same table, you simply list them next to each other like so:

(Field1,Field2,Field3,etc.)

Then, you supply the values you want stored in them providing the values in the same sequence as you've listed the fields. Hence:

VALUES(ValueForField1Here, and so on...)

 

[evictme]

Hi. Did you see what I posted earlier? I removed all your "ANDs" because that's not the correct syntax. When you want to update multiple fields in the same table, you simply list them next to each other like so:

(Field1,Field2,Field3,etc.)

Then, you supply the values you want stored in them providing the values in the same sequence as you've listed the fields. Hence:

VALUES(ValueForField1Here, and so on...)

Yes. I just saw.

Thank you. Here is the final one. THis gives me a syntax error on date?

strSQL = "INSERT INTO ClientNotes(Notes, NotesDate, ClientID,[User Updated], [Date Modified], [Time Modified]) VALUES(" & Format(Me.Home_Visit_Date, "dd-mm-yyyy") & "#," & Me.Home_Visit_Notes & "'," & Me.ClientID & Me.User_Updated & "'," & Format(Me.[Date modified], "dd-mm-yyyy") & "#," & Format(Me.[Time modified], "tttt") & "#,"

 

[evictme]

I realized that the Notes and Notes date were transposed.

Here is the final one, This one still give me an error that it is missing an operator:

strSQL = "INSERT INTO ClientNotes(Notes, NotesDate, ClientID,[User Updated], [Date Modified], [Time Modified]) VALUES(" & Me.Home_Visit_Notes & "'," & Format(Me.Home_Visit_Date, "mm-dd-yyyy") & "#," & Me.ClientID & "," & Me.User_Updated & "'," & Format(Me.Date_Updated, "mm-dd-yyyy") & "#," & Format(Me.Time_Updated, "tttt") & "#," & ")"

 

[adrianscotter]

I realized that the Notes and Notes date were transposed.

Here is the final one, This one still give me an error that it is missing an operator:

strSQL = "INSERT INTO ClientNotes(Notes, NotesDate, ClientID,[User Updated], [Date Modified], [Time Modified]) VALUES(" & Me.Home_Visit_Notes & "'," & Format(Me.Home_Visit_Date, "mm-dd-yyyy") & "#," & Me.ClientID & "," & Me.User_Updated & "'," & Format(Me.Date_Updated, "mm-dd-yyyy") & "#," & Format(Me.Time_Updated, "tttt") & "#," & ")"

Maybe it's just me but I prefer:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblUpdates")

rs.AddNew

rs![Version] = strTVerFinal
rs![General] = Me.General
rs![GeneralNote] = Me.GeneralNote
rs![Functional] = Me.Functional
rs![FunctionalNote] = Me.FunctionalNote
rs![SLU] = strUserName
rs![SLT] = Now()

rs.Update
rs.Close

Set rs = Nothing

I know they're not your field names, I copied it from one of mine, change them to suit. I prefer this format because you can see exactly what's going on and it's much easier to read when things (inevitably) go belly up!

 

[evictme]

Maybe it's just me but I prefer:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblUpdates")

rs.AddNew

rs![Version] = strTVerFinal
rs![General] = Me.General
rs![GeneralNote] = Me.GeneralNote
rs![Functional] = Me.Functional
rs![FunctionalNote] = Me.FunctionalNote
rs![SLU] = strUserName
rs![SLT] = Now()

rs.Update
rs.Close

Set rs = Nothing

I know they're not your field names, I copied it from one of mine, change them to suit. I prefer this format because you can see exactly what's going on and it's much easier to read when things (inevitably) go belly up!

Holy rusted metal, Batman!

This nailed it! Thank you so Much!!!! This kicked my ass today. Thank you.

 

[theDBguy]

Holy rusted metal, Batman!

This nailed it! Thank you so Much!!!! This kicked my ass today. Thank you.

Hi. Glad to hear you got it sorted out. Good luck with your project.

Hi Adrian, Thanks for the assist!