Solved Get records of dates to a textbox (1 Viewer)

[mib1019]

So, I'm almost there on this form. The form that calls this little popup for the user to enter dates has the txtAir_Dates on it that the simpleCSV function eventually when the popup is closed.

Sometimes, the user won't have to be opening the dialog to enter dates because there is only one. So if the popup isn't necessary, I want to use an INSERT INTO to add the date to the dates table.

Here is my code. There's a problem with my StrSQL. It runs all the way through the MsgBox statement but the record hasn't been added.

MIB1019

'write start/end date to IODetailDates table
        Dim StrSQL As String
        Dim DetailID As Long
        Dim StartDate as Date
        
        DetailID = Me.txtDetailID
        StartDate = Me.txtStartDate
        
        Debug.Print StartDate
        Debug.Print DetailID
        
        StrSQL = "INSERT INTO tblIODetails_Dates (Detail_ID, Air_Date) VALUES (DetailID , StartDate);"
        Debug.Print StrSQL
        
        DoCmd.SetWarnings False
        DoCmd.RunSQL StrSQL
        DoCmd.SetWarnings True
        
        Me.txtAirDates = Format(StartDate, "m/d")
        
        MsgBox StartDate & " added to Air Dates list."

 

[Isaac]

Try changing to:

StrSQL = "INSERT INTO tblIODetails_Dates (Detail_ID, Air_Date) VALUES (" & DetailID & " , #" & StartDate & "#)"

The sql string to be passed must evaluate to literal sql

 

[mib1019]

The tblIODetails_Dates has three fields:
1: Date_ID (autonumber),
2: Detail_ID
3: Air_Date

I think the hangup is in the VALUES part of SQL, since it skips the AutoNumber field.
MIB1019

 

[Isaac]

Did you try the code I posted

 

[mib1019]

Yes! Thanks so much!!
MIB1019

 

[Isaac]

Yes! Thanks so much!!
MIB1019

Glad to help, good luck with your project

 

[mib1019]

another approach is to have a Public function in your MainForm.
then set the ControlSource of your Dates textbox to:

=DatesToTextBox()

on main form:

Public Function DatesToTextBox() As String
    Dim v As Variant
    Dim s As String
    '
    ' replace TheSubformNameWithDatesHere with correct subform name
    '
    With Me!TheSubformNameWithDatesHere.Form.RecordsetClone
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            v = .Fields("Air Dates").value
            If Not IsNull(v) Then
                v = Split(v, " ")(1)
                v = CDate(v)
                s = s & Format(v, "m\/d") & ", "
            End If
            Debug.Print s
            .MoveNext
        Wend
    End With
    If Len(s) > 0 Then s = Left(s, Len(s) - 2)
    DatesToTextBox = s
End Function

Hi arnelgp! I want to try to get the main form function you suggested, DatesToTextBox(), working on my popup form. Do you know why I might be getting the #Size! error in the textbook control?

MIB1019

 

[mib1019]

Hello everyone! Hope today finds your safe and healthy.

I need to revisit this thread and the update text box.

One the first post in this thread, I sent a picture of my popup form to get dates. The txtDatesSelected textbox has a this function as its control source:

Public Function DatesToTextBox() As String 'returns string value of dates in recordset, comma separated, formatted 'm/d'
    Dim v As Variant
    Dim s As String
    '
    With Me!subfrmIODates.Form.RecordsetClone
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            v = .Fields("Air_Date").value
            If Not IsNull(v) Then
                v = CDate(v)
                s = s & Format(v, "m/d") & ", "
            End If
            .MoveNext
        Wend
    End With
    If Len(s) > 0 Then s = Left(s, Len(s) - 2)
    DatesToTextBox = s
    
End Function

I've had the AfterUpdate function on the actual date entry lines of the subform requery the textbox. It doesn't work. I put a command button on the main form under the text box whose on_click event also does a requery of the text box, so it should refresh. That doesn't work either.

When I get back to the calling form, I can call the popup to open again and the textbox is written, then passes its value back to the calling form.

Would love to get this working correctly. Thanks in advance for your help with this!

Mary

 

[arnelgp]

post a sample db.

 

[moke123]

Mary,
You should stick to one thread or at the very least link to the other thread.

https://www.access-programmers.co.uk/forums/threads/calculated-field-on-form-doesnt-update.312984/

 

[mib1019]

Sorry. Thought that since I had closed it as solved, this might not get any answers. Won't do it again...promise