Updating Two different tables through one form (1 Viewer)

[Pat Hartman]

I guess you didn't read the second sentence. He really wants time and time really makes sense in this context. As soon as the SQL works with just the date, it needs to be modified to work with date AND time. I offered two solutions both of which will avoid any problem caused by formatting the date as a string that includes a.m.

 

[A380b747]

to debug your code always set SQL strings to a variable

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.

Hi Majp; Thanks for your help. Now I have tried with your code but the outcome is still the same. I defined (dim) at the beginning of the event. I even tried defining at the beginning of the query but in the end, was the same outcome. What could be the reason?

 

[MajP]

You were supposed to show us the debug.print results from the immediate window.

 

[A380b747]

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.

Hi Pat;

Well, the main purpose of saving the date / time is to have a tracking of the date when a user submits the form (when a user fills out the form and save the data to another table)

I was using Now() buy there was a problem with the configuration??

How to use an append query?? I´m new on this and I´m learning

 

[A380b747]

You were supposed to show us the debug.print results from the immediate window.

this one?? This is the window that came out after running the code???

 

[June7]

No, the Debug.Print output in the VBA Immediate Window which is located below the code window, assuming it is active. Google "Access VBA Immediate Window" and "Access VBA Debug.Print". Here is one tutorial https://www.techonthenet.com/access/tutorials/vbadebug2010/debug07.php
Learn debugging techniques.

Append query creates a new record. You said you want to update existing record.

 

[A380b747]

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.

Well since the data will be stored in a table that has been already created, using "Inser Into" command will create a new register, isn't it??

So have to declere StrDate to fix the problem of the dots that are shown, right?

 

[A380b747]

No, the Debug.Print output in the VBA Immediate Window which is located below the code window, assuming it is active. Google "Access VBA Immediate Window" and "Access VBA Debug.Print". Here is one tutorial https://www.techonthenet.com/access/tutorials/vbadebug2010/debug07.php
Learn debugging techniques.

Append query creates a new record. You said you want to update existing record.

Does this work??

 

[MajP]

The string looks good so can you verify.
All table names are correct
All field names are correct
(double check all spelling)
Fecha_real is a date field
Event_ID is a numeric field
Status_ID is numeric

 

[A380b747]

The string looks good so can you verify.
All table names are correct
All field names are correct
(double check all spelling)
Fecha_real is a date field
Event_ID is a numeric field
Status_ID is numeric

I have verified everything and I cannot figure out why this code is failing to execute. I do not get it. Everything seems to be OK.

 

[MajP]

Make a query in SQL view. Post the debug.print results into the query. Try to run it. This may help show you where the problem is.

 

[MajP]

I said to double check your spelling
Evento_ID and Event_ID are not the same.

These are the type of errors that take a second set of eyes.

 

[A380b747]

I said to double check your spelling
Evento_ID and Event_ID are not the same.

These are the type of errors that take a second set of eyes.

You are right!!!!!

That was the problem!!! a single letter!!!! Now it works properly!!!!

Thank you very much for your help MajP, June7, Pat Hartman, theDBguy. I really appreciate your help

 

[theDBguy]

You are right!!!!!

That was the problem!!! a single letter!!!! Now it works properly!!!!

Thank you very much for your help MajP, June7, Pat Hartman, theDBguy. I really appreciate your help

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

 

[Gasman]

Computers are stupid. YOU have to get everything correct for them to understand you.
A human can spot the difference between evento_id and event_id, a computer cannot. Well not yet anyway. My google minis struggle between off and on

Get used to this.

 

[MajP]

If you want to include the time you can. I use this function most of my time on my dates.

so just modify the update string and replace the format I provided with
SqlDate(me.fechaReal)

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

 

[A380b747]

If you want to include the time you can. I use this function most of my time on my dates.

so just modify the update string and replace the format I provided with
SqlDate(me.fechaReal)

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

Yeah that would be useful for my program.

And what about inserting a new record on the table GC_Eventos using the last Event_ID created as the reference???

I cant related a previous Event_ID because there is no previous Event_ID. It is a totally new register. So, once you have filled out a form with the new data, when the user clicks on the button, it will create the new register on the first table (GC_Eventos) but at the same time, I would like to create a new register on another table (GC_Fecha) using that new unique ID (Evento_ID) that has been created few moments ago. All of this in the same event (Clicking the button) The second task comes after the first one is done.

Since I cannot use an Unique ID I was thinking of getting the last Unique ID (Evento_ID) using an aggregate function. (MAX) This would get me the last record created on the first table but i cannot use WHERE with aggregate functions and using HAVING might work but I don´t know.
It has not worked.

Using something like this:

Dim SQL as string
SQL = "INSERT INTO GC_Fecha (Evento_ID) SELECT (Evento_ID) FROM GC_Eventos WHERE Evento_ID = MAX(Evento_ID)"
CurrentDB.Execute SQL

But this doesn't work because of the WHERE clause. I tried using HAVING but it did not work as well.

Is it possible to do or do I need to create another independent event??

 

[MajP]

SELECT Max([Evento_ID]) AS NewID FROM GC_Eventos

SQL MIN() and MAX() Functions

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com