Datagrid Form, Driven By Cross Query - Variable field Name Problems

[AlexDoran]

Hello everyone, first of all allow me to state i am by no means an advanced Access / VB user but consider myself competent to a certain extent :banghead:

One of my roles at work is to main and develop an access database that was written by a few of my predecessors. It is used to create production paperwork, order materials, produce delivery notes & invoices etc.

One report that is particularly useful to some of the guys here is a 20 Week Production Plan, which gives them a sum of products and the value Per Job, these are sorted into Delivery Week Number.

The report is driven using a Crosstab Query, and open ReportOpen a short VB function renames and corrects the Label Headings and Datasource of the appropriate fields. The report works perfectly, no issues at all.

We then decided it might be easier and offer more functionality to migrate a copy of this report to a Datagrid Form, this way we can apply some nice Data Validation, provide system navigation through double clicking fields etc.

I thought this should have been relatively simple, however i cant seem to get it to work properly.

When viewing the Query it works as normal, the problem seems to come when trying to display it on the form correctly.

Problems noted so far:

- Query should start on the current week number, and then to 20+ Weeks, in the query this is the case, on the form it misses your current week and starts at the next?

- The For Next Loop which renames the labels and assigns control source appears to stop counting correctly at some point, im not sure if this occurs if there is no data for a particular Week Number, this was not a problem on the Report Version.

- Week Numbers are Duplicated.

Below is the code that controls the fields:

Private Sub Form_Open(Cancel As Integer)
' From Access 2000 Developer's Handbook, Volume I
' by Getz, Litwin, and Gilbert (Sybex)
' Copyright 1999.  All rights reserved.
	
' You didn't know how many columns, or what
' their names would be, until now.
' Fill in the label captions,
' and control ControlSources.
	
	Dim intColCount As Integer
	Dim intControlCount As Integer
	Dim i As Integer
	Dim strName As String
	Dim lines As Integer
	Dim MyDb As Database
	Dim rst As Recordset
	
	On Error GoTo HandleErr
' TO DO: Turn normal error handler on when this condition is finished.
	On Error Resume Next
	
	
	Dim MyQuery As QueryDef
	
'  Set database variable to current database.
	Set MyDb = DBEngine.Workspaces(0).Databases(0)
	
'  Open QueryDef.
	Set MyQuery = MyDb.QueryDefs("[qryProdValue20WeekSchedule]")
	
'  Open Recordset.
	Set rst = MyQuery.OpenRecordset()
	rst.MoveFirst
	
	
	intColCount = rst.Fields.COUNT
	intControlCount = Me.Detail.Controls.COUNT
	
        If intControlCount < intColCount Then
                 intColCount = intControlCount
        End If
	
' Fill in information for the necessary controls.
	For i = 12 To intColCount
		strName = rst.Fields(i).Name
		Me.Controls("lblHeader" & (i)).Caption = strName
		Me.Controls("txtData" & (i)).ControlSource = strName
		Me.Controls("txtSum" & (i)).ControlSource = _
		"=Sum([" & strName & "])"
		Me.Controls("txtData" & (i)).Properties("AggregateType").VALUE = 0
		Next i
		
' Hide the extra controls.
		For i = intColCount To intControlCount
			Me.Controls("txtData" & i).Visible = False
			Me.Controls("lblHeader" & i).Visible = False
			Me.Controls("txtSum" & i).Visible = False
			Next i
			
' Close the recordset.
			rst.CLOSE
			
			
			
			ExitHere:
			Exit Sub
			
' Error handling block added by Error Handler Add-In. DO NOT EDIT this block of code.
' Automatic error handler last updated at 09-09-2004 15:51:45   'ErrorHandler:$$D=09-09-2004    'ErrorHandler:$$T=15:51:45
			HandleErr:
			Select Case Err.Number
			Case Else
				MsgBox "Error " & Err.Number & ": " & Err.DESCRIPTION, vbCritical, "Report_rptProductionCapacityPlan.Report_Open"   'ErrorHandler:$$N=Report_rptProductionCapacityPlan.Report_Open
			End Select
' End Error handling block.
		End Sub

I have some screenshots of the Column Naming misbehavior.
From the Query:

https://ibb.co/1GKtHb1

Same Data in the Form:

https://ibb.co/CV0LpY9

Notice the duplicated Field Names & Mixed Order.

I get the concept of how this is supposed to be working but im not convinced about the column counting and field counting - if this is most effective way of achieving the goal. Im also not sure why it works fine on a report and not a form.

Any help would be greatly, greatly appreciated.

Thanks

Alex

 

[theDBguy]

Hi Alex. The best way to troubleshoot something like this is to step through the code during execution. Are you able to post a sample db with test data?

 

[isladogs]

Agree with DBGuy.
Use Debug lines and test what each step is doing

As a starting point, try changing this

For i = 12 To intColCount

to

For i = 11 To intColCount -1

Field (or Column) count starts at zero

 

[AlexDoran]

Agree with DBGuy.
Use Debug lines and test what each step is doing

As a starting point, try changing this

For i = 12 To intColCount

to

For i = 11 To intColCount -1

Field (or Column) count starts at zero

Thanks for this advice, i tried this and what happened was the week numbers shifted one to the left, this forced me to check the names of the textbox and label controls. What i had actually done was removed a redundant field from the Paper Report when creating the form, and i never updated the textbox control names to suit!

So the code was looking to find txtData12 & lblHeader12 to put the first week in, but those controls started at txtData13 & lblHeader13.

Man i feel like such an idiot honestly, thank you for sending me down the path to my success I have spent way too many hours on this for such a simple fix! Atleast now i feel like i fully understand how it works and could implement it in other situations.

Alex

 

[isladogs]

I had a feeling that would help slot everything into place
We've all done similar things so don't beat yourself up....

Good luck with the rest of your project

 

[Johnnie_Rosenberger]

Hey Alex,

My work asked me to do the same thing, where I need to drop a crosstab query into a form, where they can further drill down into tailed information.

I've been beating my head against a wall for a few days now trying to figure this out. Would you have any examples on how this is done?

Thank you in advance!!

 

[gemma-the-husky]

I would have a pre-determined table with the production columns named W0, W1, W2, W3 etc, as references to the week numbers.

You bind your form/report to these week numbers, but somewwhere you store a "start date", so you update your column labels based on 7 day increments of the start date.

If you actually have a week number calendar, then you could update to the week number, rather than the date.

So todays date (W0) is either 20/5/2019 (or Week 21), and you increment all the other week labels based on this. so W1 is presented as either 27/5/2019 or Week 22, etc

all you need to do is insert the data into the correct column of the working table.

 

[AlexDoran]

Do you mean that you want to open Child Records based on what is displayed in the Crosstab?? Im not sure how you would go about that, i opted to use datagrid view where we could easily apply some conditional formatting, from here they can double click into certain fields (Eg Job Number) to be taken to the relevant forms (Production Jobs) already filtered on that specific job

https://prnt.sc/nqzaxa

In terms of examples, all that is happening is that i am specifying a date range to filter the results on and then have an expression field to generate the week number. The week number is then actually a column heading in the crosstab and can then be used as a data source in the report / datagrid.

The code further up then renames Labels (Columns) to the week number and sets the Datasource to match the week numbers in the Crosstab.

Query:

http://prntscr.com/nqzdgv

Note. I am excluding record before 2006 as this database is very very old and i have found omitting these results seems to speed up the query.

Thanks

 

[Johnnie_Rosenberger]

Thank you gents. Appreciate the guidance.

I work for a company that works to assist the homeless community and getting them temporary housing and medical services. I've looked high and low for any examples, as my coding skill for VBA is limited, to say the least and even open to venmo $$ if someone can provide me a form that can do the following:

Top subform that shows a summary crosstab with dynamic headers. The headers is comprised of various medical services provided by doctors (ie, dental, psyche, trauma, etc) Row headers would be names of doctors(we work with about a dozen). The value would be dollars billed for each service to the city.

The bottom subform would provide detail to each claims if the user clicks on doctor's name on the top form. Key would be ClaimID.

Taking a one last shot here. I've even bought an Access reference book but can't seem to make sense of it.

Thank you again.

 

[AlexDoran]

Hi Johnnie,

I am by no means an expert in Access but i am sure it would be fairly easy to implement what you want without using any (or very very little) VBA at all.

As you say a crosstab query can easily handle the Summary section of your desired form, you just need to create the relevant tables and fields and setup the crosstab correctly, its fairly intuitive in letting you decide which fields are Columns & Rows.

In terms of pulling up details about specific claims, i would be using a series of Combo / List boxes that the user can drill down to the specific records they want to see. For example, first they would pick from a Combo box which Doctors they would like to view claims for, the next Combo box would have a Query Driven Data Source based on what Doctors is selected in first combo box and only display results that match. You could even through in some Date fields to help them narrow the results down more. You can reference the Value of form controls in Queries to use it as criteria.

Make sure to spend a bit of time just laying out your table & field design so that you have a clear road map of the relationships you need to create in the database.

Alex