Combobox returning loaded value rather than selected value (1 Viewer)

GregMiller

Registered User.
Local time
Today, 09:05
Joined
Dec 5, 2019
Messages
18
I am trying to use a combobox in a form to allow the user to select from a list of activities and then by clicking a button on the same form to produce a report about the receipts and expenditure for that activity. The value from the combobox is stored in a table which is then used to select records using the where statement in the total line of a query. The result I am getting is that the report about receipts and expenditure is produced not for the activity selected by the user but for the activity that happened to be shown in the combobox when the form was opened. So it looks as though the value stored in the table is the value when the form is opened rather than the value subsequently selected by the user. Obviously I am doing something wrong but I can’t work out what.
I would be grateful for any help. As this is my first cry for help I am not sure what further information is needed to solve my problem but if a screenshot of something would help please let me know.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:05
Joined
Oct 29, 2018
Messages
21,358
Hi. Typically, if you're using a combobox to open and filter a report, the combobox would be unbound. It sounds like you may be using a bound combobox instead. Could you please verify this? Thank you.
 

GregMiller

Registered User.
Local time
Today, 09:05
Joined
Dec 5, 2019
Messages
18
Yes DBguy the combobox is bound to the table where I store the value. Perhaps what I need to know is how to retrieve a value selected from an unbound combobox.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:05
Joined
Oct 29, 2018
Messages
21,358
Yes DBguy the combobox is bound to the table where I store the value. Perhaps what I need to know is how to retrieve a value selected from an unbound combobox.
To refer to the value in a control on a form, you can use a form reference like:

Forms!FormName.ControlName

Is that what you mean?
 

GregMiller

Registered User.
Local time
Today, 09:05
Joined
Dec 5, 2019
Messages
18
When I have tried to reference the combo box directly in the format you describe in my where statement in the query selecting the records to be included no records are selected and so when the report is opened the report structure is there but otherwise the report is empty.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:05
Joined
Oct 29, 2018
Messages
21,358
When I have tried to reference the combo box directly in the format you descbe in my where statement in the query selecting the records to be included no records are selected and so when the report is opened the report structure is there but otherwise the report is empty.
Can you post the SQL statement for your report's query? Thanks.
 

GregMiller

Registered User.
Local time
Today, 09:05
Joined
Dec 5, 2019
Messages
18
This is the SQL statement using the table field to select the records to be included:

SELECT [Receipt Categories].Description, [Cost Centres].Description, Sum(Receipts.Amount) AS SumOfAmount, Events.Description, Events.Date
FROM [Event Focus] INNER JOIN (Events INNER JOIN ((Receipts INNER JOIN [Receipt Categories] ON Receipts.Category = [Receipt Categories].Code) INNER JOIN [Cost Centres] ON Receipts.CostCentre = [Cost Centres].Number) ON Events.Number = Receipts.Event) ON [Event Focus].[Event Chosen] = Receipts.Event
WHERE (((Receipts.Event)=[Event Focus]![Event Chosen]))
GROUP BY [Receipt Categories].Description, [Cost Centres].Description, Events.Description, Events.Date;

and this is the SQL statement attempting to select using the combo box directly:

SELECT [Receipt Categories].Description, [Cost Centres].Description, Sum(Receipts.Amount) AS SumOfAmount, Events.Description, Events.Date
FROM [Event Focus] INNER JOIN (Events INNER JOIN ((Receipts INNER JOIN [Receipt Categories] ON Receipts.Category = [Receipt Categories].Code) INNER JOIN [Cost Centres] ON Receipts.CostCentre = [Cost Centres].Number) ON Events.Number = Receipts.Event) ON [Event Focus].[Event Chosen] = Receipts.Event
WHERE (((Receipts.Event)=[Forms]![Event Accounts Menu]![Combo30]))
GROUP BY [Receipt Categories].Description, [Cost Centres].Description, Events.Description, Events.Date;
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:05
Joined
Oct 29, 2018
Messages
21,358
Okay, assuming you were saying the first SQL statement was in the Row Source of the Combobox and the second SQL is what's behind the report, then the second SQL's criteria is trying to match the Event field to what's selected in the Combobox, but the Combobox is probably selecting the Description field instead. You would want to probably select the Event ID in both cases. Is the Event field a number or text field?
 

GregMiller

Registered User.
Local time
Today, 09:05
Joined
Dec 5, 2019
Messages
18
I am sorry I didn't make this clear in my earlier post. The two SQL statements are alternatives that I have tried to make the selection of records for the report. The first is using the value from the combo box after it has been assigned to a field in a table (ie the combo box bound to the table). The second is attempting to use the value in an unbound combo box to make the selection. The first option results in the report being based on the value in the combo box when the form is loaded and the second appears to result in a null value being used to select the records as none are selected.

The SQL statement in the row source of the combo box for both options is:

SELECT [Events].[ID], [Events].[Number], [Events].[Description] FROM Events ORDER BY [ID];

The Event field is a number.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:05
Joined
Oct 29, 2018
Messages
21,358
Hi. It might be easier to fix this if we could see what you're looking at. Are you able to post a sample copy of your db with test data?
 

GregMiller

Registered User.
Local time
Today, 09:05
Joined
Dec 5, 2019
Messages
18
Thank you very much for your patience DBguy. I have followed the instructions on the forum to strip down the database for upload. So in the stripped down version go from the Main Menu to the Reports Menu and then the Event Receipts and Expenditure Menu where the malfunctioning combo box can be found. The data I have left in the database is good for events 3, 4 and 5.
 

Attachments

  • Events Accounts For Upload.zip
    70.4 KB · Views: 61

theDBguy

I’m here to help
Staff member
Local time
Today, 09:05
Joined
Oct 29, 2018
Messages
21,358
Thank you very much for your patience DBguy. I have followed the instructions on the forum to strip down the database for upload. So in the stripped down version go from the Main Menu to the Reports Menu and then the Event Receipts and Expenditure Menu where the malfunctioning combo box can be found. The data I have left in the database is good for events 3, 4 and 5.
Hi Greg. Thanks for posting your db. You don't need the criteria in your query, but I left it there. Try taking it out to see if it makes a difference using the updated version I attached below. Cheers!
 

Attachments

  • Events Accounts For Upload.zip
    68 KB · Views: 53

GregMiller

Registered User.
Local time
Today, 09:05
Joined
Dec 5, 2019
Messages
18
Thank you very much DBguy your revised version works fine. Can you tell me what you changed to get the result I was looking for? I will need to know that to make the changes in the live database.

I haven't tried taking out the criteria in the query yet. Isn't it the criteria that selects the records to be included in the report for a given event?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:05
Joined
Oct 29, 2018
Messages
21,358
Thank you very much DBguy your revised version works fine. Can you tell me what you changed to get the result I was looking for? I will need to know that to make the changes in the live database.

I haven't tried taking out the criteria in the query yet. Isn't it the criteria that selects the records to be included in the report for a given event?
Hi. Please try out the suggestion first to confirm what I thought I saw in your setup. If the result is the same, then you have a couple of ways to go, so we can discuss your options.
 

GregMiller

Registered User.
Local time
Today, 09:05
Joined
Dec 5, 2019
Messages
18
OK I have taken out the criteria statement in the queries for both receipts and expenditure. It still works but I would be fascinated to know how the correct records are selected without the criteria statements.
 

Attachments

  • Events Accounts For Upload revised v2.zip
    80.3 KB · Views: 61

theDBguy

I’m here to help
Staff member
Local time
Today, 09:05
Joined
Oct 29, 2018
Messages
21,358
OK I have taken out the criteria statement in the queries for both receipts and expenditure. It still works but I would be fascinated to know how the correct records are selected without the criteria statements.
Okay, that's what I thought but couldn't test it earlier. The reason for that is you're using a bound form to select the event. What is the purpose of the Event Focus table? Will it ever have more than one record in it? I am thinking you don't need it, in the grand scheme of things.
 

GregMiller

Registered User.
Local time
Today, 09:05
Joined
Dec 5, 2019
Messages
18
Yes I was just using the Event Focus table as a means of storing the Event Number for the report the user wanted to produce. It has no other purpose.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:05
Joined
Oct 29, 2018
Messages
21,358
Yes I was just using the Event Focus table as a means of storing the Event Number for the report the user wanted to produce. It has no other purpose.
Okay, if it was me, I would delete that table and adjust everything that uses it. See attached...
 

Attachments

  • Events Accounts For Upload.zip
    65.2 KB · Views: 60

GregMiller

Registered User.
Local time
Today, 09:05
Joined
Dec 5, 2019
Messages
18
Thanks DBguy I have followed that approach in the live database and it works. What puzzles me is why when I tried that approach before the queries failed to select any records, as I mentioned at #5. But the main thing is that I am now getting the result I was looking for and so thanks very much for your help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:05
Joined
Oct 29, 2018
Messages
21,358
Thanks DBguy I have followed that approach in the live database and it works. What puzzles me is why when I tried that approach before the queries failed to select any records, as I mentioned at #5. But the main thing is that I am now getting the result I was looking for and so thanks very much for your help.
Hi Greg. Glad to hear you got it to work. Good luck with your project.
 

Users who are viewing this thread

Top Bottom