On Open event not working.

[DSBO]

Hi all,

I am having a strange problem with my companies access database. It was a database I have inherited from a previous employee.

There is a report used to view NCR data, depending on what NCR number is entered. So on load a box pops up asking for a number, this then goes off to look at the NCR ID and display all the information ready for printing. However, anything greater and including 20001 will not display, it is just the report template and no info. 20000 and below works fine. Is there a limit or somthing I am missing that could cause this?

Here is the code in the editor:

Option Compare Database

Private Sub Report_Load()

End Sub

Private Sub Shift_Click()

End Sub

and this is the record source code I believe is running this report:

SELECT [NCR Section A].[NCR ID], [NCR Section A].[Employee Name], [NCR Section A].[Employee Name2], [NCR Section A].Supplier, [NCR Section A].[P/Order / Machine], [NCR Section A].Date, [NCR Section A].Shift, [NCR Section A].[Raised By], [NCR Section A].[Quantity Checked], [NCR Section A].[Qty Non Conforming], [NCR Section A].Material, [NCR Section A].Description, [NCR Section A].[Drawing Number], [NCR Section A].[Drg Revision], [NCR Section A].Customer, [NCR Section A].[Customer Order Number], [NCR Section A].[Works Order Number], [NCR Section A].[Item Number], [NCR Section A].[Serial Number], [NCR Section A].[Non Conforming Feature], [NCR Section A].[Actioned By], [NCR Section A].[Hold Location], [NCR Section A].[ID 1], [NCR Section A].DateModifiedA, [NCR Section A].TimeModifiedA, [NCR Section A].[Picture 1], [NCR Section A].[Picture 2], [NCR Section A].[Picture 3], [NCR Section A].[Picture 4], [NCR Section A].[Picture 5], [NCR Section A].[Picture 6], [NCR Section A].[NCR TYPE], [NCR Section B].*, [NCR Section C].*, [NCR Section C2].*, [NCR Section D].*, [NCR Section E].*, [NCR SECTION F].*, [NCR Section A].[Serial Number2], [NCR Section A].[Serial Number3], [NCR Section A].HoldArea, [NCR Section B].DevCode, [NCR Section B].CauseCode, [devgrp] & " - " & [devdescr] AS DevD, [causegrp] & " - " & [causeDescr] AS CauseD, [NCR Section A].Status, [NCR Section A].ConcCode, [NCR Section A].ConcDate, [NCR Section A].ConcOpInit, WorkArea.WorkAreaResource, IIf(IsNull([WorkArea]![WorkAreaResource]),[NCR Section A]![P/Order / Machine],[WorkArea]![WorkAreaResource]) AS WARX FROM (((((((([NCR Section A] INNER JOIN [NCR Section B] ON [NCR Section A].[NCR ID] = [NCR Section B].[NCR ID]) INNER JOIN [NCR Section C] ON ([NCR Section A].[NCR ID] = [NCR Section C].[NCR ID]) AND ([NCR Section B].[NCR ID] = [NCR Section C].[NCR ID])) INNER JOIN [NCR Section C2] ON ([NCR Section A].[NCR ID] = [NCR Section C2].[NCR ID]) AND ([NCR Section B].[NCR ID] = [NCR Section C2].[NCR ID])) INNER JOIN [NCR Section D] ON ([NCR Section A].[NCR ID] = [NCR Section D].[NCR ID]) AND ([NCR Section B].[NCR ID] = [NCR Section D].[NCR ID])) INNER JOIN [NCR Section E] ON ([NCR Section A].[NCR ID] = [NCR Section E].[NCR ID]) AND ([NCR Section B].[NCR ID] = [NCR Section E].[NCR ID])) INNER JOIN [NCR SECTION F] ON [NCR Section A].[NCR ID] = [NCR SECTION F].[NCR ID]) LEFT JOIN tblCause ON [NCR Section B].CauseCode = tblCause.CauseCode) LEFT JOIN tblDev ON [NCR Section B].DevCode = tblDev.DevCode) LEFT JOIN WorkArea ON [NCR Section A].[P/Order / Machine] = WorkArea.WorkAreaCode WHERE ((([NCR Section A].[NCR ID])=[Enter NCR Number]));

I appreciate any help as im very new to access and im only just learning VB code.

Many thanks

Rob.

 

[vbaInet]

The sql statement is hard to read. There's a facility called code tags, wrap codes in code tags.

If you run the sql statement as a query do you get over 20000 records? How many records exactly if it does go over?

Welcome to AWF by the way

 

[SteveSchapel]

Rob,

It appears from the SQL of the query that the NCR ID value requested would have to exist in all these tables:
NCR Section A
NCR Section B
NCR Section C
NCR Section C2
NCR Section E
NCR Section F

Maybe more! Apart from the fact that this indicates a poorly designed database (yes, I know it wasn't you! ), perhaps this will give you a clue as to why the record for the requested number is not showing up.