Updating Two different tables through one form (1 Viewer)

A380b747

New member
Local time
Today, 07:15
Joined
Jun 11, 2022
Messages
28
Hi everyone:

Hope you are doing well. I´m reaching out to you through this forum because I´m stuck with this and I need your help.

So, I have this form in which the user fill out with all the information required. And when the user clicks on the button, the data table is updated with the news entries.
However, I have been requested to register the date / time at the moment the user clicks on the button. The problem is that I need to save the data (date format) in another table (not the same table where the all data from this form is stored). How can i stored the data from a textbox in this form to another table?

I have used the code below to update the table (GC_Eventos) when someone clicks the button . And this works fine.

1654910652583.png



How can I add the data (date format) from a textbox (the one with the date) in the same form but then store the information in another table (in a column with date/time data type ????? I have tried to replicate the same code I have been using but it does not work.

1654911731158.png


I would appreciate your help

Regards
 

June7

AWF VIP
Local time
Today, 04:15
Joined
Mar 9, 2014
Messages
5,423
Run an INSERT action SQL if you want a new record.

CurrentDb.Execute "INSERT INTO tablename(fieldname) VALUES(#" & Me.datetextbox & "#")
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:15
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

I agree, it's probably easier to run an append query, but opening a recordset, as in your current code, should have worked as well.
 

A380b747

New member
Local time
Today, 07:15
Joined
Jun 11, 2022
Messages
28
Run an INSERT action SQL if you want a new record.

CurrentDb.Execute "INSERT INTO tablename(fieldname) VALUES(#" & Me.datetextbox & "#")

Hi June7;

Thanks for replying back. Actually there are already records on the other table, so I just want to fill the field of date / time

I tried to do a SQL query but it also did not work: Since the record already exists, I´m using the Update command.

1654921650608.png



I want to fill out the field "Fecha_real" within the GC_Fecha table using Evento_ID and Status ID (fixed) as my conditionals / parameters for the specific record. I do not know what part of the query is wrong?? I just want that the textbox data (date) appears in this specific record of the field.

Do you know what i´m doing wrong??

Regards

1654921768982.png


Regards
 

A380b747

New member
Local time
Today, 07:15
Joined
Jun 11, 2022
Messages
28
Hi. Welcome to AWF!

I agree, it's probably easier to run an append query, but opening a recordset, as in your current code, should have worked as well.

I have tried using either Sql query or Recordset but at the moment, none of them have worked :cry:
 

June7

AWF VIP
Local time
Today, 04:15
Joined
Mar 9, 2014
Messages
5,423
Should post code as text, not image. I would have to type your complete SQL instead of copy/paste.

There is an extra quote mark in the concatenation. Remove the quote mark following the second # character.
 

A380b747

New member
Local time
Today, 07:15
Joined
Jun 11, 2022
Messages
28
Should post code as text, not image. I would have to type your complete SQL instead of copy/paste.

There is an extra quote mark in the concatenation. Remove the quote mark following the second # character.


CurrentDb.Execute "UPDATE GC_Fecha SET Fecha_real = #" & Me.Fecha_real & "# WHERE Event_ID = " & Me.Evento_ID & " AND Status_ID = 1"

I ran the code and this error message came out: I think it is close to be solved since the message actually can read the date :unsure:

1654925324333.png
 

A380b747

New member
Local time
Today, 07:15
Joined
Jun 11, 2022
Messages
28
a. m. should be am

Why are there dots and space?

I have no idea. It is just an empty text box that has the function Now() as default value and it takes the system date as value. The dots are placed there automatically. Should I use another command / function?

Well, my computer has the same dots. Even though I have changed the date / time configurations on my computer, Access still displays the same format.

Actually it is ok if I only add the date. Maybe there is another function I can use instead of Now()

1654926689506.png
 
Last edited:

June7

AWF VIP
Local time
Today, 04:15
Joined
Mar 9, 2014
Messages
5,423
Date() will provide just date, no time, parts.

My computer does not show dots and space and I have never seen Access insert them.
 

A380b747

New member
Local time
Today, 07:15
Joined
Jun 11, 2022
Messages
28
Date() will provide just date, no time, parts.

My computer does not show dots and space and I have never seen Access insert them.

thank you June7. Indeed, I have changed to Date() and the time has been removed. So I tried again an now this error message came out: :cry:

CurrentDb.Execute "UPDATE GC_Fecha SET Fecha_real = #" & Me.Fecha_real & "# WHERE Event_ID = " & Me.Evento_ID & " AND Status_ID = 1

Which parameters are missing?? The query has two parameters that shows "1" in order to stored the value correctly. Status_ID can take the value of another textbox but i prefer to set a fixed value (1) instead of using the textbox that will always display 1.

1654962320881.png


1654962684638.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:15
Joined
May 21, 2018
Messages
8,463
to debug your code always set SQL strings to a variable

Code:
Dim strSql as string
strSql = "UPDATE GC_Fecha SET Fecha_real = #" & format(Me.Fecha_real,"MM/DD/YYYY") & "# WHERE Event_ID = " & Me.Evento_ID & " AND Status_ID = 1"
debug.print strSql
currentDb.execute strSQl

Post the strSql so we can see how it resolves. Also format your date to mm/dd/yyyy.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:15
Joined
Feb 19, 2002
Messages
42,970
Are you using an unbound form or are you duplicating the whole record? What is the purpose of saving the date/time? You can't use Date() if you want the time also. You must use Now().

You might have better luck if you don't use embedded SQL. Use an append query. That gets the date/time from the form field.
 

June7

AWF VIP
Local time
Today, 04:15
Joined
Mar 9, 2014
Messages
5,423
Are you using an unbound form or are you duplicating the whole record? What is the purpose of saving the date/time? You can't use Date() if you want the time also. You must use Now().
OP started with Now() and that caused issue so decided time part is not important and is now trying Date().
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:15
Joined
Feb 19, 2002
Messages
42,970
OP started with Now() and that caused issue so decided time part is not important and is now trying Date().
And that's a "solution"?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:15
Joined
May 21, 2018
Messages
8,463
And that's a "solution"?
No the OP is not using NOW the OP is using the field Me.Fecha_real , which obviously has a stored time component. However the solution is to properly format the international date.
Code:
 #" & Me.Fecha_real & "#
to
Code:
 #" & format(Me.Fecha_real,"MM/DD/YYYY") & "#
 

June7

AWF VIP
Local time
Today, 04:15
Joined
Mar 9, 2014
Messages
5,423
Fecha_real could be name of field or textbox or both. Regardless, value is obtained by textbox DefaultValue set to Date() - previously Now() which caused issue. The mystery is why Access is inserting dots and space in the am/pm part - result is not a true date/time value which errors in SQL and Format doesn't seem to work with it.

I had not noticed the international date structure in data. Review http://allenbrowne.com/ser-36.html
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:15
Joined
May 21, 2018
Messages
8,463
The mystery is why Access is inserting dots and space in the am/pm part - result is not a true date/time value which errors in SQL and Format doesn't seem to work with it
It is not that complicated. If the date is not in an ambigous format MM/DD/YYY or YYYYMMDD then it will give a syntax error or a parameter error. Need to properly format the date.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:15
Joined
Feb 19, 2002
Messages
42,970
However, I have been requested to register the date / time at the moment the user clicks on the button.

Just a reminder of the objective.

Substituting Date() for Now() is not a solution.

I made a suggestion that avoids having to format the date and so should avoid the issue with the a.m., assuming that is what is causing the problem. I doubt the OP tried it though.


Make a querydef and save it:
Insert Into YourTable (YourDate, YourFK)
Values Forms!YourForm!YourDate, Forms!YourForm!YourFK;

Then run the querydef rather than an SQL String.

Assuming that Forms!YourForm!YourDate is bound to a date/time data type OR if unbound has a format property that indicates the control should hold a date, you don't need to format anything. The internal double precision number will be used so no formatting will get in the way.

Another solution assuming you insist on using embedded SQL Strings would include concatenating a string formatted as yyyy/mm/dd with a space followed by a string formated as Short Time could work since Short Time uses the 24 hour clock and so doesn't use the AM/PM designators.

dim strDate as String
strDate = Format(YourDate, "yyyy/mm/dd") & " " & Format(YourDate, "Short Time")

Then use strDate bracketed with #'s when building the SQL string.
 
Last edited:

June7

AWF VIP
Local time
Today, 04:15
Joined
Mar 9, 2014
Messages
5,423
In post # 9:
Actually it is ok if I only add the date. Maybe there is another function I can use instead of Now()
OP should at least get the SQL working with Date() and then decide if want to delve into going back to fixing and including time part.
 
Last edited:

Users who are viewing this thread

Top Bottom