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

mib1019

Member
Local time
Today, 10:09
Joined
Jun 19, 2020
Messages
88
I have a popup form for a user to populate a simple subform with dates. Here is a picture of the subform...
Capture.PNG


My goal is to gather the list of dates into that textbox, called txtDatesSelected, in mm/dd format, comma separated. There could be up to 20 dates in the list. The subform control is subfrmIODates. The txtDatesSelected and the txtAirCount values will be passed back to the calling form.

Thanks in advance for your awesome help, as always!
MIB1019
 

mib1019

Member
Local time
Today, 10:09
Joined
Jun 19, 2020
Messages
88
Okay, that works, but with no spaces after the commas and need the format of the dates to be simply 'm/d'.

My inexperience is showing :/
Thanks for the advice there!

MIB1019
 

Isaac

Lifelong Learner
Local time
Today, 09:09
Joined
Mar 14, 2017
Messages
8,738
For the spaces, I think you may just need to change one line:
old:
Code:
strCSV = strCSV & strDelim & .Fields(0)
new:
Code:
strCSV = strCSV & strDelim & " " & .Fields(0)
For the date format, check out the Format() function for use in your query/SQL that you are passing to the function. Or use Month() and Year() concatenated with a "\" in between.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:09
Joined
Oct 29, 2018
Messages
21,358
For the spaces, I think you may just need to change one line:
old:
Code:
strCSV = strCSV & strDelim & .Fields(0)
new:
Code:
strCSV = strCSV & strDelim & " " & .Fields(0)
For the date format, check out the Format() function for use in your query/SQL that you are passing to the function. Or use Month() and Year() concatenated with a "\" in between.
Hi. No need to change the code. I tried to make it flexible to allow for any delimiters. If you change the code now and then later decide you need a different delimiter, you will have to change the code again. To add a space between the commas, you simply use something like this:
Code:
SimpleCSV("SELECT...", ", ")
For example, if instead of commas and spaces, you need a carriage return (new line), you simply do this:
Code:
SimpleCSV("SELECT...", Chr(13) & Chr(10))
Cheers!
 

mib1019

Member
Local time
Today, 10:09
Joined
Jun 19, 2020
Messages
88
Spaces are okay now, but I don't see how to add format to the dates in SQL. I did a query with that SQL statement, brought it to Query design, added a format, which works, but don't see SQL reflecting that format.

I also have a leading space on the first date.

Thanks!
MIB1019
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:09
Joined
Oct 29, 2018
Messages
21,358
Spaces are okay now, but I don't see how to add format to the dates in SQL. I did a query with that SQL statement, brought it to Query design, added a format, which works, but don't see SQL reflecting that format.

I also have a leading space on the first date.

Thanks!
MIB1019
Hi. Did you try using the Format() function? For example,
Code:
SimpleCSV("SELECT Format([DateField], 'm/d') FROM TableName", ", ")
 

mib1019

Member
Local time
Today, 10:09
Joined
Jun 19, 2020
Messages
88
Thanks for that! SQL needed apostrophes in the format function, rather than quotes. On to figuring out how to trim the leading space.

y’all are THE BEST!
mib1019
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:09
Joined
May 7, 2009
Messages
19,169
another approach is to have a Public function in your MainForm.
then set the ControlSource of your Dates textbox to:

=DatesToTextBox()

on main form:


Code:
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:09
Joined
Oct 29, 2018
Messages
21,358
Thanks for that! SQL needed apostrophes in the format function, rather than quotes. On to figuring out how to trim the leading space.

y’all are THE BEST!
mib1019
Maybe use the Trim() function?
 

mib1019

Member
Local time
Today, 10:09
Joined
Jun 19, 2020
Messages
88
Thanks, LTrim works perfectly. Form is almost ready...yay!

On entering dates in the subform, I've got keyup and keydown events working properly, to move up and down in the list, but the enter key causes the dialog popup to close entirely. What's up with that?

MIB1019
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:09
Joined
Oct 29, 2018
Messages
21,358
Thanks, LTrim works perfectly. Form is almost ready...yay!

On entering dates in the subform, I've got keyup and keydown events working properly, to move up and down in the list, but the enter key causes the dialog popup to close entirely. What's up with that?

MIB1019
For that, we'll probably have to see it in action. Are you able to share a sample copy of your db?
 

Isaac

Lifelong Learner
Local time
Today, 09:09
Joined
Mar 14, 2017
Messages
8,738
On entering dates in the subform, I've got keyup and keydown events working properly, to move up and down in the list, but the enter key causes the dialog popup to close entirely. What's up with that?
Do you have any button (or any control) on your form that is designed/coded to close the form?
 

mib1019

Member
Local time
Today, 10:09
Joined
Jun 19, 2020
Messages
88
The main form popup has Cancel and OK buttons, both of which have to be clicked to run the code behind them. There is no button in the subfrmIODates. The subform has a label in the header, a field in the footer that counts the number of records in the current list, and the txtAirDate and the DetailID fields in the detail section. txtAirDate is the only enabled field on the subform. The Enter Key behavior on txtAirDates field to default Default.

This database is pretty full of data, so it would take some doing to send a sample. But I will if necessary.

MIB1019
 

Isaac

Lifelong Learner
Local time
Today, 09:09
Joined
Mar 14, 2017
Messages
8,738
The main form popup has Cancel and OK buttons, both of which have to be clicked to run the code behind them
Do they have a "default" property=Yes? Default buttons take the Enter key. This could be what is happening with " the enter key causes the dialog popup to close entirely ". I don't know for sure? But just pointing out buttons don't always have to be clicked. For default to take the Enter key I think it depends on which form has the focus at the moment, but with subforms and main forms depending on where the user is clicking that could be going back and forth all the time.
 

mib1019

Member
Local time
Today, 10:09
Joined
Jun 19, 2020
Messages
88
You were right, pisorsisaac.

I changed the Default on both buttons to No and the form stays open. I still think it's odd behavior that the cursor doesn't just move to the next field with the Enter key, which is what I want, to make entry of a list of dates easier (one-handed). Guess I can use a KeyPress event to do that, right?

MIB1019

P.S. Thanks for the attention. It never takes long to get a problem worked out on this forum. It's great!
 

Isaac

Lifelong Learner
Local time
Today, 09:09
Joined
Mar 14, 2017
Messages
8,738
You were right, pisorsisaac.

I changed the Default on both buttons to No and the form stays open.
Glad it worked!!

I still think it's odd behavior that the cursor doesn't just move to the next field with the Enter key, which is what I want, to make entry of a list of dates easier (one-handed). Guess I can use a KeyPress event to do that, right?
I believe there are some Database and Form properties you could probably better leverage rather than using Key-based events. (I just think the key based events might dig you into a more complex hole). I am not sure whether the Options>Current Database>Enter key behavior will help in this context or not (can't remember). I think most people approach this as Tab key behavior, though, and train users to use Tab.
 

mib1019

Member
Local time
Today, 10:09
Joined
Jun 19, 2020
Messages
88
another approach is to have a Public function in your MainForm.
then set the ControlSource of your Dates textbox to:

=DatesToTextBox()

on main form:


Code:
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

Arnelgp,

I like your function idea so I am trying that. I have substituted to Me!SubfrmIODates.Form.RecordsetClone (the subform control name). I changed .Fields("Air Dates") to the control source, so .Fields("Air_Date").

I am getting a #Size! error in the textbox?

Thanks,
MIB1019
 

Users who are viewing this thread

Top Bottom