Replies

Hans,

I was able to go back to a Office 97 version of this report and was able to import what appears to be successfully. However, (and I think this is related to the original corruption), I continually get a “Missing Module Error” when compiling. It seems to be related to the old binder reference in the old database. I get a can’t find/missing reference to “MSBDR9.OLB”. No matter what I do I cannot clear this error. The actual reference is no longer available in the libraries because the new machines we have did not upgrade Office but did a fresh install of 2003. So now I am at a point where the database will not compact and repair nor compile. Any sugeestions?

Thanks
Kevin

Hans,

Thanks for your help. I might be able to go back in time to a point at which this was created about 8 years ago in access 97. Do you think there will be any issues importing fom a 97 to 2003 database directly?

Thanks Again
Kevin

Thanks Hans,

That worked and it is attached. By the way, why does 97 format reduce the size even further?

Hans,

I have deleted everything except the queries that support the report and the report. I have deleted all other tables, forms, modules etc. and the zipped file size is still 206 K. Not sure what to do? Max attachment size is 100 K.

I think I can do that, but I know I’ve tried in the past with out sucess. It is huge and even without records is much larger than can be sent. I’ll have to zip but before I do that, could you/would you want just the reports collection or the entire tables/forms/macros/reports/queries etc. Tables are in a back end and pretty much everything else is in front end.

Thanks
Kevin

Thanks Hans,

I will pick up with that in the morning.

Kevin

Hans,

Before I do that, (huge under taking), I tried eliminating parts of that event one at a time. and if I leave everything else alone and just comment out the OLE lines (as below)…

…
Private Sub Check354_AfterUpdate()
DoCmd.SetWarnings False

‘DoCmd.Requery

‘ Attach TAR Report
If (Forms![Splash Screen]![vSecurity] = 70) Then
If Me!Check354 = True Then

Dim mySQL As String
Dim vPricingPackage As String
vPricingPackage = Forms![Splash Screen]![vLastPricing]

DoCmd.OpenForm “frmSelectFileXP”, acNormal, , , , acDialog

‘ oleTechnicalReport.Class = “Excel.Sheet” ‘ Set class name.
‘ Specify type of object.
‘ oleTechnicalReport.OLETypeAllowed = acOLELinked
‘ Specify source file.
‘ oleTechnicalReport.SourceDoc = Me.vTARReportName
‘ Specify data to create link to.
‘TechnicalReport.SourceItem = “R1C1:R25C15”
‘ Create linked object.
‘ oleTechnicalReport.Action = acOLECreateLink
‘ Adjust control size.
‘ oleTechnicalReport.SizeMode = acOLESizeZoom

Me.Desk_TAR_Complete = True

Forms![Splash Screen]![vTechReportName] = Me.vTARReportName

End If

RunCommand acCmdSaveRecord

… more code

It works fine. Is there something unique that I might be missing as far as the OLE routine? Am I better off having the users get to the report some other way than linking to it in this way?

Thanks

Kevin

Thanks Hans,

Just tried that on and I get the same message.

kevin

thanks Hans,

Your comments are appreciated. I zoomed in on # 3 real quick. Is there anyway to set the OLE to multiple records at the same time. In the current environment, I can only display one record on the form. I have been able to set the ole using the method in # 3. I was hoping to be able to set the same object to many records at the same time. Is there a query I can run in the back ground after setting the first one and update the other records based on the first?

To give you an idea of what I am trying to accomplish is that when a record is processed, it might be processed by itself or it may be grouped with other records and processesd. If done by itself I am trying to assign the ole object to that record (Already done that). It is this grouped process that has messed things up. When a group is processed, the same ole object applies to the entire group.

Any suggestions?

Thank You
Kevin

Hans, Thank You, working now !!!!!!!!!! I was not thinking that the SQL would look like that. I was thinking that the strHolder variable should just pass the same thing that you would hand type into the query itself. Once I passed that mental block I was able see more clearly. Thank You for all of your help.

Kevin

Hans, I got it to run and update the correct field with:

mySQL = “UPDATE Pricing ” & _
“SET Pricing.Modified_Pricing_ID = ‘ModifiedID’ ” & _
“WHERE Pricing.Pricing_ID=” & strHolder

However, it updated all of the records (6) of them vice the (3) identified in the strHolder collection.
strHolder is passing

…”N0117.018.1N2100″ Or “N0117.018N2100” Or “N0117.018.1N2101” to the sql.

I am close I think, I just am not comprehending what I am doing wrong with …
… “WHERE Pricing.Pricing_ID=” & strHolder

Thanks
Kevin

Hans, making headway but still falling short. I managed to put the sql statement together as below:

mySQL = “UPDATE Pricing INNER JOIN [Pricing Detail EB] ON Pricing.Pricing_ID = [Pricing Detail EB].Pricing_ID ” & _
“SET Pricing.Modified_Pricing_ID = ‘ModID’ & vlastname & vcode , ” & _
“Pricing Detail EB.Modified_Pricing_ID = ‘ModID ‘& vlastname & vcode ” & _
“WHERE Pricing.Pricing_ID=strholder;”

I am getting a message saying syntax error in update statement.
I tried removing brackets but cannot tell where I fell off the truck.
I have looked at many examples but now I’m not sure if the error is truly in the Update statement or if the whole SQL has the syntax error. ANy chance you can take a quick look?

Thanks
Kevin

10th nickel: I have reduced the sql to something very simple see below:

mySQL = “UPDATE Pricing ” & _
“SET Pricing.Modified_Pricing_ID = ‘ModifiedID’ ” & _
“WHERE Pricing.Pricing_ID= ‘” & strHolder & “‘”

it runs and executes but does not update any records. Am I back to the variable strHolder as the culprit?

Thanks Hans,
I can follow the thought process in your example, my only uncertanty is passing the result to the query.

Dim strWhere As String
Dim i As Long
‘ Loop through list box items
For i = 0 To Me.lstMyListBox.ListCount – 1
If Me.lstMyListBox.Selected(i) = True Then
strWhere = strWhere & “,” & Me.lstMyListBox.ItemData(i)
End If
Next i
If strWhere = “” Then
‘ Nothing selected – get out now!
Exit Sub
Else
‘ Remove leading comma
strWhere = Mid(strWhere, 2)
‘ construct WhereCondition
strWhere = “FieldName In (” & strWhere & “)”
‘ Open report
DoCmd.OpenReport “rpt Estimating – PrintOut”, acViewPreview, , strWhere
End If

I am thinking that the last line would open the query instead of the report listed, but does strWhere pass to the query as an argument of calling the query similar to how the report in the example works?

Thanks
Kevin

WOW that was a fast response. Thanks. The string is generated using a list box and actually is related to the questions that started this string. I am trying to establish a second ID for the records in the list box that will relate the list box items. For example, Each item in the list box has its own unique ID, but now I want to relate them using a secondary ID. My intent was to run an update query that assigns that ID. So I was able to identify the records that require the update and put that in the text box on the form. The ID I want to assign works great if the query runs, but obviously I am falling short on passing that parameter to the update query. I will take a look at the sample you pointed out to me and try to incorporate that. In the mean time, what were the other methods you refered to if the text box was filled from a list box. In this case it came from a multi selct list box set to extended.

Hans Thanks for the help
Kevin

Thanks Hans, it worked just fine. If you have time for one more, I am having trouble getting an update query to run wher it is taking the criteria from a text box on a form. The text box containns the string…

“N0117.018.1N2100″Or”N0117.018N2100″Or”N0117.018.1N2101” quotes included.

the text box is on [Forms]![Open Pricing by Gov_ID Number]![vStringexp] with vStringexp as the name.

When I use the syntax [Forms]![Open Pricing by Gov_ID Number]![vStringexp] in the criteria to find the recordds to update it will not execute. However, if I copy and paste that same string directly from the text box to the query criteria, it runs just fine. What am I missing here?

Thanks
Kevin