Use new query in form

[Homegrownandy]

I have a form for jobs, a subform for work days. I have also made a query that uses the Job_ID on that form and counts how many times that ID appears in the workdays table.



so.. a job has 2 assigned days it comes back with the number 2...

What I want:
the name for the day to be that value +1 automatically.

So if no days are assigned to a job its 0+1 = Day 1
If one day is assigned 1+1= Day 2
and so on.

I want this name to then be added to a textbox on the form called day_title.

relevant info:
query name: daycounterQ
form: JobF
subform: workdays


If you need anymore info to assist just ask.

how I think it will work:

the generated number is displayed on the form on a hidden text box. Then some vba code will use the value of that text box and concatenate it with the work days to give us our day name.

I have no idea how to do any of this. How would I even reference a new query on the form?

All advice much appreciated, Andy.

[June7]

You want to assign a sequential number to records for each job? This is generating a custom unique identifier (in this case, unique for each job, not the table) - a common topic. https://www.accessforums.net/databas...try-21975.html

Is it really necessary to save this to table? A report can dynamically generate this sequence number with RunningSum property of textbox. No VBA needed.

Essentially same topic as your earlier thread https://www.accessforums.net/access/...ter-55425.html

[Homegrownandy]

I have also made a query that uses the Job_ID on that form and counts how many times that ID appears in the workdays table.

As I said here June I have that part working. I said it in the thread you linked to and here. That's not the question.

I already have the ability to get the number of days assigned to a job. That's in its own query..

I don't know how to use that query on my form. I want to use that value to name days automatically.

In the last part of my post there I describe what I want to do with that value after I get it on the form. I want to add 1 to the number and put the word days before it.

I don't need to save the generated number anywhere..

My query will use the Job_ID on that form and counts the associated days.. all I need is the query result to be put there.

I'm assuming its the row source I need to change. So I can include that, and ill include the sql for the query.. I don't know if that's what I need to put together this is the purpose of the question.

Row source of the sub form: (this does not have the job ID on its the main form that does. but its where I want the number)

SELECT [Workdays].[Day_ID], [Workdays].[Staff_ID], [Workdays].[Day_Title], [Workdays].[Scheduled_Start], [Workdays].[Additional_Information], [Workdays].[Consecutive_Days], [Workdays].[Job_ID], [SiteT].[Site_Name] FROM (SiteT INNER JOIN JobT ON [SiteT].[Site_ID] =[JobT].[Site_ID]) INNER JOIN Workdays ON [JobT].[Job_ID] =[Workdays].[Job_ID];

Query:

SELECT Workdays.Job_ID, Count(Workdays.Day_ID) AS CountOfDay_ID
FROM Workdays
GROUP BY Workdays.Job_ID
HAVING (((Workdays.Job_ID)=[Forms]![JobF]![JOBID]));

[Homegrownandy]

Speaking as someone who knew nothing about databases a few months ago it can be very confusing for both the person asking the question and the person answering. Often people asking don't know what they are asking.. They don't know the names of things they need to or procedures. So how can they ask?

But its a two way road, I attempt to be as clear as possible after the countless times I've been refused help because I didn't include relevant information (even though I say in most posts if you need more just ask). Your answer is nothing to do with the question. All you had to do was read the first line and you can see that I have a counter working. I also say this on the thread you link to.

I appreciate any form of help, if there is some.

[June7]

I did read your post but apparently did not comprehend the true need. If you already have the sequencing accomplished and just need the aggregate count included, options:

1. possibly use Count() function in form footer section

2. build report using Sorting & Grouping features with aggregate calcs in group and report header/footer sections

3. join the GROUP BY query to the other query by linking on the Job_ID fields - however, the dataset will likely be not editable so probably not what you want for a form

4. DLookup()

[Homegrownandy]

Okay, thanks anyway. Ill look into this further over the weekend

[Homegrownandy]

Thanks for your suggestions June. I have this working now.

Count on the footer as you suggested with +1 to that value. Then the default value of my text box is.

="Day " & [day_count]

every new day is now named automatically.