Replies

Thanks Hans
I did do the search and found that post earlier. I tried adopting it to fit my situation and am running into a syntax problem. I am trying the following code attached to an add button:

Dim ctlList As Control
Dim ctlList2 As Control
Dim varItem As Variant
Dim mysql As String

‘ Return Control object variable pointing to list boxes.
Set ctlList = Me.vAvailPricings
Set ctlList2 = Me.vPricingsSelected

‘ Enumerate through selected items.
For Each varItem In ctlList.ItemsSelected
‘ Add to selected list.
mysql = “INSERT INTO tblPricingsSelected (Pricing_ID) VALUE (” & ctlList.ItemData(varItem) & “);”
DoCmd.SetWarnings False
MsgBox mysql
DoCmd.RunSQL mysql
DoCmd.SetWarnings True

Next varItem
ctlList.Requery
ctlList2.Requery

I get a run time 3134 error
Syntax error in insert into statement.

I think it is because the value of ctlList.ItemData(varItem) is a text value but am not usre because everything I have tried (double quotes, etc.) has not worked.

Any Idea?

Thanks
Kevin

Hans, thanks

I tried to decrease the hardware acceleration but our working environment here has that part locked out so if I can’t get IRM folks to allow access or install updated driver if it exists I will probably live with it. The tab names seem to come back as soon as you roll the cursor over the top.

Thanks again for the help.

Edited by HansV to replace 3.9 MB bitmap file in zip file with 12 KB .png file containing the relevant part of the image. Saves disk space and superfluous clicks.

Hans,
Thank You for your help. I have made great progress since yesterday. This tabbed form seems to flow better than the old method (never really got into that) but I am curious as why the tab names sometimes go blank or turn white when I tab through the control. Have you seen this before? see attched screen shot.

thanks Hans, I will give it a try.

Thanks Hans,

The code is run in the on open event of the Pricing Form. The Pricing form is a main form and the tab control resides on it. There are other subforms on the tab control but the controls in question are not part of them. All of the controls in question are visible, enabled and not locked. Hope that helps.

Thanks Hans,

I’m not sure if I was clear at first. When the form initially opens the record source is set to a different query. The second subform is used when a user wants to slice and dice the record set in different ways. So I change the record source to handle the different ways the records can be displayed. It is triggered in the on change event for the Option “Tar Type Cuts” which is located on the second form on the right. It may be used once or more than once as the user slices and dices the record set view. “qryPricing DetailTarCuts” is a select query based on a table where the underlying records reside and it consists of specific criteria relating to combo boxes on the second form to filter the records. The second form by the way is not tied to the parent. I have attached a screen shot of part of the query so you can see the criteria if it helps (the sql is a mile long).

Not sure if I am answering your question(s)

Thanks
Kevin

Thank You very much

Hi, thanks for the reply, I latched onto your concept for counting but do not understand how your code points to column A. If I wanted to apply this to other columns, what parts of the require changing. I have played with the various lines of code trying to point to different columns but am not changing all that need to be changed because my results are all over the place.

Thank You

Kevin

Thank You,

Since I am trying to do perform some tasks in the on open event, is there a way to incorporate this in a VBA loop? This is a situation where there may be 6 cells in the column or there may be 300 cells in the column. I was hoping to use vba to determine the number of rows with data by using …

…Sheets(“qrySSTARDataforHardCopyReport”).Select
Range(“A2”, “A1000”).Select

‘count rows for loop
myCount = Application.CountA(Selection)…

… and then use the myCount variable to determine how many different values were in a specific column within that original range.

Thank You
Kevin

Thanks Hans, I thought of that and tried the following…

… IIf([Forms]![Pricing]![frmTARTypeCut].[Form]![vBCLength]=”1″,[Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center],Like [Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center] & “*”) …

… I can get a single criteria to work. In other words if I remove the IIF statement and just use …

… [Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center] …
or
… Like [Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center] & “*”) idependently, it works, but as soon as I tie them to the IIF statement it bombs.

My most recent attempt was that since the query looks at more than one combo box on that subform and that this one combo box is the only one set up with an IIF statement, that it is the combination of the two criteria that is messing it up.

The below is the complete SQL …

… SELECT [Pricing Detail TAR].Pricing_ID, [Pricing Detail TAR].Rate_ID, [Pricing Detail TAR].CLIN, [Pricing Detail TAR].Ship, [Pricing Detail TAR].POP, [Pricing Detail TAR].Burden_Center, [Pricing Detail TAR].TAR_Est_Hours, [Pricing Detail TAR].TAR_Matl_Esc, [Pricing Detail TAR].TAR_Matl_Desc, [Pricing Detail TAR].[TAR Hours Cut], [Pricing Detail TAR].[TAR De-Esc Material Cut], [Pricing Detail TAR].[TAR Esc Material Cut], [Pricing Detail TAR].[TAR Position Hours], [Pricing Detail TAR].[TAR De-Esc Mat Position], [Pricing Detail TAR].[TAR Esc Mat Position]
FROM [Pricing Detail TAR]
WHERE ((([Pricing Detail TAR].Pricing_ID)=[Forms]![Pricing]![Pricing_ID]) AND (([Pricing Detail TAR].CLIN) Like [Forms]![Pricing]![frmTARTypeCut].[Form]![cboCLIN] & “*”) AND (([Pricing Detail TAR].Ship) Like [Forms]![Pricing]![frmTARTypeCut].[Form]![cboShip] & “*”) AND (([Pricing Detail TAR].POP) Like [Forms]![Pricing]![frmTARTypeCut].[Form]![cboPOP] & “*”) AND (([Pricing Detail TAR].Burden_Center)=IIf([Forms]![Pricing]![frmTARTypeCut].[Form]![vBCLength]=”1″,[Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center],([Pricing Detail TAR].[Burden_Center]) Like [Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center] & “*”)));

I may not be able to seperate these records out the way I am thinking.

Thanks
Kevin

Hans, Thanks again.

Thanks Hans, That makes a lot of sense. I presume I can make that calculation either through an update query or by stepping through the records one at a time. My first choice would be the update query. Also if the cuts should all happen on the first records or the last record, is there a way to remove all the hours from say the first reord until it is reduced to 0 then move to the next record if there are any more left to cut? Example: Of those original 35 hour over the five records were cut to 25 hours but the cuts were applied in record order starting with the first, and say the first record had 3 hours and the second record had 10 hours, I would need to apply 3 of the 10 hour cut to the first record and 7 of the 10 hour cut to the second record. In this example, would I be better looping through the recordset using a variable to keep track of the number of hours I am reducing each record by until all 10 hours being cut are used up?

That is a little different from the first question and I am trying to figure out if there is a one fit calculatio/procedure that would apply to both situations, or I would need to set up a choice button on a form to to direct the cuts to happen in each scenario? Sorry for being so general in the example, but I am not sure yet how I am going to implement this in a user friendly way.

Thanks
Kevin

Thanks Hans,

Up and running.

Thanks Hans,

I’m sorry about the confusion, I was not reading the code correctly. In the previous version I was talking about, there was a make table query and the reference was set to the table and not the query. The table names and query names were nearly identical. I am not very good a SQL, so if I re-think this and change the query to a make table query, run this ahead of time and set the reference to the table that was created it should work?

Hans,
qryRouteSheet is a select query that has “[Forms]![TX-Abroad Packages]![File Number]” in the criteria block where [Forms]![TX-Abroad Packages]![File Number] is referencing a text box on an open form. Hope that helps?