Solved show two records in one text box (1 Viewer)

murray83

Games Collector
Local time
Today, 15:29
Joined
Mar 31, 2017
Messages
613
OK so have just about got this down, the calendar isn't of my making ( i wish came from this site, here is the download link Cal ) but have got it to display people but the issue i have and if you look at the data in the included table, a few days has more than one person off

i have gone through the code and i sort of understand it, where it mentions find first, i think i need to change it here maybe so it displays more than one person

all help apperciated and cheers keep safe everyone, its hot out 35C here in Daventry UK
 

Attachments

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 09:29
Joined
Feb 28, 2001
Messages
17,334
What is the goal in English? Showing us something you admit you didn't make means we have TWO things to work through. What THEY wanted to do and what you wanted to do. Your thread title is a hint, but not really enough.

I see that the calendar in question is unbound, and that much is good because if it were bound, you would have a devil of a time shown data from two records in one textbox. However, since it is unbound, you can do something to pull in the desired records and then do some string concatenation.

More than that? What you showed was fairly limited.
 

murray83

Games Collector
Local time
Today, 15:29
Joined
Mar 31, 2017
Messages
613
ok sorry if i was a bit vague,

i would like it to check the table, tblInput and then populate the calendar as it does, but if there is another record for that date with a different colleague name, then i would like that also displayed

and just maybe( but not as important), depending on the number of colleagues listed for that day, some formatting for the day so people can see that, that day is out of the question for anymore holiday requests

hope that helps, somewhat
 

bob fitz

AWF VIP
Local time
Today, 15:29
Joined
May 23, 2011
Messages
3,972
I believe you need to import the form called frmInputBox and possibly the other form called frmChoose from the db that you got the form frmCalender from
 

murray83

Games Collector
Local time
Today, 15:29
Joined
Mar 31, 2017
Messages
613
I believe you need to import the form called frmInputBox and possibly the other form called frmChoose from the db that you got the form frmCalender from
yes i belive thats how they had it working, you could click on a date and then type in some details which would then displayed on the calender.

but i have allready the function for people to request dates, which then has the tblInput table populated by a query based on shift = H, so have no need for the data entry stuff, just more of a visual aid and it was the best one i had found
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:29
Joined
Sep 21, 2011
Messages
6,360
First thing that springs to mind is use Allen Browne's Concatenate function or theDBGuys function to get all the employees for a date, then just present that concatenated data.?

That would be the easiest way I believe as very little to change, just the sql statement?

Not sure why this is repeated?, but does no harm
Code:
f("text" & i) = rs!InputText
f("text" & i) = rs!InputText
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:29
Joined
Sep 21, 2011
Messages
6,360
Yes, but theDBguy has a similar function. You can search here for it. It has csv in the name I think, or he will chip in.
Allen's is easy enough to use. It must be, as I was able to use it. :D

Once you get them in, you can split them to see how many for a certain day and then colour them differently or stop any more being added if need be.? Though a Dcount() on your form would probably stop any more being added.?
 

murray83

Games Collector
Local time
Today, 15:29
Joined
Mar 31, 2017
Messages
613
Yes, but theDBguy has a similar function. You can search here for it. It has csv in the name I think, or he will chip in.
Allen's is easy enough to use. It must be, as I was able to use it. :D
thats good to here i like easy
 

murray83

Games Collector
Local time
Today, 15:29
Joined
Mar 31, 2017
Messages
613
easy, hmmm maybe im a bit thicker lol

ok so i have made the module and saved it then placed this in a text box to try and test it but all i got was errror

Code:
=ConcatRelated("InputDate", "tblInput", "InputText = " & [InputText])
but get #Name
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:29
Joined
Sep 21, 2011
Messages
6,360
From the comments?
Code:
  'Purpose:   Generate a concatenated string of related records.
    'Return:    String variant, or Null if no matches.
'Arguments: strField = name of field to get results from and concatenate.
' strTable = name of a table or query.
' strWhere = WHERE clause to choose the right values.
' strOrderBy = ORDER BY clause, for sorting the values.
' strSeparator = characters to use between the concatenated values.
From what you have posted you want all the inputtexts for a date?, not the other way around?
 

murray83

Games Collector
Local time
Today, 15:29
Joined
Mar 31, 2017
Messages
613
erm... i want to concenate the colleagues into one row, when they have the same day off

and for the where shouldnt i have the date for the calender as well
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:29
Joined
Sep 21, 2011
Messages
6,360
erm... i want to concenate the colleagues into one row, when they have the same day off

and for the where shouldnt i have the date for the calender as well
strField = name of field to get results from and concatenate.
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:29
Joined
Sep 21, 2011
Messages
6,360
Here is the one time I have had to use it.
This is in a textbox of a report, that shows all crew for a certain date, sorted by surname
Code:
=ConcatRelated("CrewName","QryCrewStandby","QryCrewStandby.Rank = """ & [Rank] & """ AND QryCrewStandby.On_Date = #" & Format([Dated],"mm/dd/yyyy") & "#","CrewNameRev")
Which produces
1596211966984.png
 

murray83

Games Collector
Local time
Today, 15:29
Joined
Mar 31, 2017
Messages
613
ok so for the date i should be using this as well #
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:29
Joined
Sep 21, 2011
Messages
6,360
ok so for the date i should be using this as well #
No! :(
You do not appear to be grasping how it works.?
It will concatenate a fields contents for a criteria.?

In your case it is the InputDate as the criteria. the field you want to concatenate is InputText. ?

I was initially thinking of using it in the sql statement, but the " characters in the function call clash with building the string, and I cannot get the syntax correct. Any expert wants to jump in a show how that can be done, I'd be grateful :)

So I would say, once you get the correct records (there is a hint there :)) then use the function to get the concatenated string to populate the textbox.
This method is simpler, if not the most efficient. :(

Is that any clearer?

If you do that, then you can get this

1596216571632.png
 
Last edited:

murray83

Games Collector
Local time
Today, 15:29
Joined
Mar 31, 2017
Messages
613
oooooo so it can work

ok, ok i'll figure it out

just one question is the textbox still unbound ??
 

murray83

Games Collector
Local time
Today, 15:29
Joined
Mar 31, 2017
Messages
613
No! :(
You do not appear to be grasping how it works.?
It will concatenate a fields contents for a criteria.?

In your case it is the InputDate as the criteria. the field you want to concatenate is InputText. ?

I was initially thinking of using it in the sql statement, but the " characters in the function call clash with building the string, and I cannot get the syntax correct. Any expert wants to jump in a show how that can be done, I'd be grateful :)

So I would say, once you get the correct records (there is a hint there :)) then use the function to get the concatenated string to populate the textbox.
This method is simpler, if not the most efficient. :(

Is that any clearer?

If you do that, then you can get this

View attachment 83917

ok so tried this

Code:
Expr1: ConcatRelated([InputText],[tblInput],[InputDate])
then it says this to me, no.png, in attached,

But i have the module, see see.png
 

Attachments

Gasman

Enthusiastic Amateur
Local time
Today, 15:29
Joined
Sep 21, 2011
Messages
6,360
I would normally say set a breakpoint and walk through the code, but you are not even using it the way it has been shown? :(, so that is never going to work. Does that code of your look anything like the function code call?

You are still not using it, in the way it has been documented. :(
Read the documentation, that is why it is there, rather than just chucking parameters at it and hoping for the best.

I have already tried it as a separate field in a simple query to check the syntax and that works, BUT as I mentioned before, because of the clash with double quotes, I changed to getting the concatenated data after the recordset had been opened. That method produces the picture I uploaded.

If I use your syntax in the query it prompts for tblInput? , that should give you a clue?
 

murray83

Games Collector
Local time
Today, 15:29
Joined
Mar 31, 2017
Messages
613
so ok

have ran through with F8 and break points have found where it opens the recordsource to populate the calender

so that picture you put up is being ran off a query and the textboxes are still unbound

ps

but mine isnt from form as it has many dates, as its the calenadr
 

Attachments

Last edited:

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom