Runtime Error - Opening form based on subform selection

I don't think the problem is with the statement itself, more within the AuctionForm.

I assumed you can open the form manually and it works.

Do you have any code that runs on the formOpen event (I should believe so) ?

Do you have an ActivieX control on your form ?

Is it possible you are trying to interact with your activex control in the code ?

Yes, I can open the AuctionForm manually and it works just fine.

No, there is no code that runs on AuctionForm's formOpen event, and I haven't used ActiveX anywhere.

Just to help find where is the error, have you try your openning code with a valid ID hardcoded

Less say you do have an eBayNum = 12345 then try this:

>  Private Sub ViewAuction_Click()
>  DoCmd.OpenForm "AuctionForm", , , "EbayNum = 12345"
>  End Sub

Replace 12345 with a valid value.  This is assuming the eBayNum is numerical.  If it is defined as a string, then you have to encapsulate its value in quotes.

If it works, the problem is  where the application tries to resolve  Me.OrderSubform.Form!EbayNum

If it does not work, the promble is where Access tries to apply to filter on the form.  At least will have a better idea wher to look.

The code generates the same error even when I hardcode a valid EbayNum value into the DoCmd.OpenForm statement.

Again, some questions to eliminate possibilities:

eBayNum is numerical ?

eBayNum is part of the table or query bound to the form AuctionForm ?

eBayNum column is bound to a control on the form, even if it is not visible ?

EbayNum values are numerical.

EbayNum is part of the table AUCTION that is bound to the form AuctionForm. It is the primary key of the AUCTION table.

EbayNum is bound to a control on AuctionForm.

Have you tried to open AuctionForm and then manually put a filter on the form using Access menus (again, just trying to figure out what is going on)

Um... No, I haven't.  Please give an example how to go about this, i.e. which filter button, what kind of filter, etc., and I'll give it a try.  Thanks.

From the standard Form menu, click on Record > Filter > Filter by form

The data in your form will be erase, in the EBayNum text box, enter the number you are looking for then, on the tool bar, click on the applu button (a funnel symbol).

In theory, the filter you passed as a parameter on the DoCmd.openform is apply in a similar way by Access.

When I try this manual filter, I do see the appropriate record appear in the form, but an error window immediately pops up in front of it: "The column prefix 'AUCT-PMT subform' does not match with a table name or alias used in the query."

If I click "OK" (since that's all I *can* do), I get an explanatory window that informs me that Microsoft Access did not apply the filter since I "entered an invalid data type in one of the fields."

After clicking through all the error messages, I am left looking at the record I was filtering for.

The form "AUCT-PMT subform" is used as a subform on the main form  "AuctionForm". It is bound to the AUCT-PMT table, which holds data related to payments on auctions.  Why is it being dragged into this? What query is being referenced in the first error message?

In the query, did you put a criteria referencing the form ?

Check the child-master link expression for the subform.  

Obviously, I don't have access to your application, all I can do for now is to give you ideas on things to validate or test.  But I do believe we move forward a little bit.

What query are you referring to?

As for the Link Child/Master settings of the AUCT-PMT subform, they are both EbayNum.

Oh - here's the View I created in SQL Server that the "AUCT-PMT subform" is bound to:

SELECT     dbo.AUCTION.EbayNum, dbo.AUCTION.EndDate, dbo.LOT.LotNum, dbo.LOT.Description, dbo.LOT.WarehouseLoc
FROM         dbo.AUCTION INNER JOIN
                      dbo.LOT ON dbo.AUCTION.LotNum = dbo.LOT.LotNum

(Going to lunch now - be back in an hour or so...)

Sorry, I was on the impression that AuctionForm was bound to a query but I reread the thread and found that you did mentionned it is bound to the table.

Do you have combo boxes or list boxes that could be looking for information in the subform ?

No combos or lists.

The eBayNum you are trying to get to, does it have records in the subform ?

Be sure to try with a number that has at least one record in the sub form

I did your manual filtering suggestion looking for an EbayNum that did have some records in the AUCT-PMT subform, and also looked for an EbayNum that did not.  The errors I received were identical for each.

The AUCT-PMT table, which is bound to the subform "AUCT-PMT subform", consists of the following columns: Date-Time, EbayNum, Amount, Method, and CCFee. The PK of this table is the combintaion of Date-Time and EbayNum. EbayNum is also a FK from the AUCTION table. Payments are recorded into this table via the AUCT-PMT subform on the main AuctionForm -- but only after an Auction has closed and the winner makes a payment. Therefore, many Auctions that can be viewed in the AuctionForm (that is, those that haven't closed or whose winners haven't yet sent a payment) will not have any associated records in the AUCT-PMT subform.

Sounds ok.  I was curious because when a main form reference a control or fields in a subform and there are no record in the subform, then there is an error because the control is not instanciated.  So I ws wondering if this was the case here.  It is not because you have the error with or without records in the subform...

Any new thoughts, then, on where the problem lies? I am stumped, and quite frustrated.

I am stumped as well.

Are you willing to try a different approach that may involved a little more coding but is more robust ?

If so, I will like you to try this first and tell me if it works.  When you click on the button, the form should go to specified record.

Add a button on the AuctionForm, caption Test, name cmdTest

In the OnClick event of the button type the following (replace 12345 with an existing value):

   Dim rs As Recordset
   Set rs = Me.RecordsetClone
   rs.Find "eBayNum = 12345"
   If rs.EOF Then
      MsgBox "Not Found"
   Else
      Me.Bookmark = rs.Bookmark
   End If

I am leaving for the night, I will get back tomorrow.

Good luck

That worked just fine. I put the command button directly onto the main AuctionForm and it went right to the specified record.  Would similar coding, then, be used if I were to put the button onto the main OrderForm instead?

Whoops....... I didn't see your longer post right away.... I'll try your synchronization technique and get back with you tomorrow. Thanks for the help so far!

I will be on vacation next week.  I will answer your postings, if any, the week after.

Bye

lol - And I'm out of the office today!

I'll post as soon as I can, and await your response.

Thanks, and have a good time!

Ragoran, I have had other issues pop up that have prevented me from trying out your suggestion. I will get to it soon. Thanks again, and please don't forget me.  :)

Ok, I will wait your comments.

You suggested some code for both the Auction form and the Order form.  To what controls/objects should I assign this code, and to what events (OnClick, etc.)?

Also, I am confused.  Looking at this again, the button that would open an Auction record will actually be sitting on the Order form. The button is pressed, the Auction form opens, the user views/modifies the Auction record, the user closes the Auction form, and then they are looking at the Order form once more.  Why is any code required on the Auction form at all?

This is how I program all my form interactions.  It ia base on a OO programming principle of "low coupling" which could be interpreted as "each object is responsible for his internal behavior" and "Objects interact with each other using specifics methods which exposed the available behavior to other objects"

The code in the Auction form is the public method that allows other objects (e.g. forms) to ask the Auction form to go to a certain record.  It is implemented into the Auction form because how this is done is its responsibility.  It is, to my opinion, bad programing practive to refer to the name of a form fields or control into another form, althoug it could be programed that way.  Using the method I promote, I found it is very easy to reuse the form in many places and that changes has less consequences.

In my example, the only code in the Order Form that should be at a specific place is the following.  You may have to replace ViewAuction by the name otf the button the user will click to see the Auction.

****** modify the click event
Public Sub ViewAuction_Click()
   
   anAuctionForm.GotoAuction Me.OrderSubform.Form!EbayNum

End Sub

There is also a line of code to put into the Form_close event,  This is only doing some cleanup to make sure there are no obkect left open in memory when the form is closed.

The Property AuctionForm can be put anywhere in the form module.  I am using a property so the module variable that holds a reference to the AuctionForm will get "set" whenever but only if needed.


Did I managed to remove some confusion ?

Ragoran

Cleared up some confusion (the example of OO programing helps), but created some new.

"Anywhere in the form module?" So that chunk of code is not directly connected to some control on the Auction form?

--Galisteo8

Exact,

this line should be put BEFORE any other subs or function code, typically after the "option explicit" directive:

******** Now, in the Order Form, add this code:

'declare as a module variable
Private moFrm As Form_AuctionForm


And this piece of code is, as you guess, not associated to any specific events.  It is used by the "ViewAuction_click" event code (anAuctionForm. line) .  It can be put anywhere in the module.  Paste it at the end to make sure it is not in the middle of another subs.


Property Get anAuctionForm() As Form_AuctionForm

   Dim x As String
   
   If moFrm Is Nothing Then
      Set moFrm = New Form_AuctionForm
   End If
   
   On Error Resume Next
   x = moFrm.Name
   If Err.Number > 0 Then
      'form has been closed
      Set moFrm = New Form_frmCIE_Company
   End If
   
   Set anAuctionForm= moFrm
   
End Property

*********** Okay...I’m getting ready to try this out. Got a few questions at
*********** the bottom of this post before I do. Let me review first…
*********** So in the code module for the AuctionForm, I put the following:

Public Sub GotoAuction(pEbay As Long)

   Dim rs As Recordset
   Set rs = Me.RecordsetClone
   rs.MoveFirst
   rs.Find "eBayNum = " & pEbay
   If rs.EOF Then
      MsgBox "Not Found"
   Else
      Me.Bookmark = rs.Bookmark
      Me.Visible = True
   End If

End Sub


*********** Then, in the code module for the OrderForm, I put at the very top:

'declare as a module variable
Private moFrm As Form_AuctionForm


*********** At the bottom of the OrderForm code module, I put:

Property Get anAuctionForm() As Form_AuctionForm

   Dim x As String
   
   If moFrm Is Nothing Then
      Set moFrm = New Form_AuctionForm
   End If
   
   On Error Resume Next
   x = moFrm.Name
   If Err.Number > 0 Then
      'form has been closed
      Set moFrm = New Form_frmCIE_Company
   End If
   
   Set anAuctionForm= moFrm
   
End Property


*********** Then, I create the command button on the OrderForm, and I
*********** set this as the OnClick event of the button:

Public Sub ViewAuction_Click()
   
   anAuctionForm.GotoAuction Me.OrderSubform.Form!EbayNum

End Sub


***********Finally, this Close event:

Private Sub Form_Close()

   Set moFrm = Nothing
   
End Sub


*********** Question 1 – That last bit of code… it gets set as a Close event on what:
*********** the command button? Or just put in the AuctionForm code module?

*********** Question 2 – Are all my other understandings correct regarding
*********** where to place the code?

*********** Question 3 – How do I actually put code into a form’s code module
*********** without assigning it to an object? (That is, how do I simply open
*********** the code module for editing?)

*********** Question 4 – Your suggestion for the OnClick event is a Public Sub.
*********** By default when I create the OnClick event it is a Private Sub. What
*********** is the difference?

Q1:  The close event for the form will be trigger however the form is closed: By click on the X icon, by using the File:close menu or by issuing a docmd.close instruction on the click event of a button. This is the "beauty" of evetn programming, you don't care how it happens, just that it does.

Q2:  Your understanding is correct

Q3: You can click on the "code" button of the tool bar while in design mode, you can press ALT-F11 or you ask to add code to an event put just leave it empty, the editor will remove the header for you.

Q4:  Ok with private sub for the click event.  Actually' I should have type it this way in the first place.  Private sub/function/property are only visible in the "object" they are typed in.  Public ones are visible by all.

Thanks for answering my latest questions.  Looks like I'll test this out tomorrow.

Actually, I've got time to test this out now. Regarding the Close Event ( Set moFrm = Nothing): Which form do I use this on?

In the OrderForm, it is to remove any outstanding reference to the Auction Form.  These are the cause of "memory leak" you can hear of sometimes.

Okay, I got all the code properly placed. Upon my first test-run, I encountered the following error:

"Run-time error '424': Object required."

The error was generated from the OnClick event of the command button (which I called ViewAuction2). Below is the code I used; note that the name of the subform containing the list of auctions is OrderLots Subform. Because this is a multi-word name, I enclosed it in [ ].

Private Sub ViewAuction2_Click()
   anAuctionForm.GotoAuction Me.[OrderLots Subform].Form!EbayNum  
End Sub

What would be causing this error? My brackets?

As I review the code snipets, I notice an error that I posted before.

In the code:

' *******************
Property Get anAuctionForm() As Form_AuctionForm

   Dim x As String
   
   If moFrm Is Nothing Then
      Set moFrm = New Form_AuctionForm
   End If
   
   On Error Resume Next
   x = moFrm.Name
   If Err.Number > 0 Then
      'form has been closed
      Set moFrm = New Form_frmCIE_Company   '<<<<<<<
   End If
   
   Set anAuctionForm= moFrm
   
End Property


change Form_frmCIE_Company    to   Form_AuctionForm


It may or may not fix the error you have.  To help us find the problem, we will slightly change the onClick event code to :


Private Sub ViewAuction2_Click()
   dim oF as Form_AuctionForm
   set oF =    anAuctionForm
   oF.GotoAuction Me.[OrderLots Subform].Form!EbayNum  

End Sub

Now the error should happen on either the line set oF = ... or the next one.  

An error now occurs on this line in the Property statement in the OrderForm code module:

      Set moFrm = New Form_AuctionForm

The error is:
"Run-time error'7782': You can't create a new instance of this form or report while it is in Design view."

This error is rather self explantory.  You have the Auction Form open in design view and, of course, Access can't use it while your are designing it.  In other words, you have a lock on the form.

Close the form in deisgn view then try your code again.

Hm... I checked that already, to make sure I didn't have it open anywhere.  Wait a sec....

Okay, I just restarted Access, and now the problem does not recur. Woo hoo! Your synchronization works well, far better than the other "workable" code I tried out last weekend. My 2nd crack at it had the Auction Form open up to the selected Auction, but then the user couldn't peruse other Auction records -- it was like a filter had been applied limiting the Auction Form to *only* displaying that one record. Your code, on the other hand, opens the Auction Form to the selected record, and yet the user can still scroll through other Auction records.

Could you explain again (in simple terms) precisely what each step of your code is doing? It would help me to apply this solution to other situations if I understood it better.  Thanks!

I am happy to talk to someone who wants to understand, not just blindly apply a "recipe".

In the Auction form, the GotoAuction method use typical code to move to a specific record.  You do a search on the recordsetclone which happens in the background.  If there are no match, then the user won't see a flickering screen.  If there is a match, the form will show the new record when we set its bookmark property to the one from the recordset clone.  A bookmark could be compared to a record number but is it not a number.  It is a *temporary* string used to identify a unique row in a recordset.  NEVER store a bookmark for more than the scope of a procedure.

The GotoAuction method is declared public.  This means that any piece of code in your application could ask the Auction form to show a specific record.


In the Order form, we want to open the Auction form than ask it to go to the specified record using the GotoAuction method.  To do that we need a reference (variable) to the Auction Form.  There are many ways to obtain such a reference.  The one I suggested as the advantage of being self contained and not forcing the form to reopen itself every time it is called, so it is a bit faster at run time.

Using an instance of the form in a variable instead of a DoCmd.Open instruction allows us to interact with the form before it is made visible to the user.  Again, this is to prevent display flickering.

But when you set instance of form in a variable, the form only exists as long as the variable is active.  If you use a variable in a sub, the variable is "killed" when the subs ends.  Furthermore, the code in the sub won't wait the auction form to be closed.  So we need to keep the form reference in a variable that will survive the execution of the OnClick event sub.  This is why it is defined as a module variable (moForm).  You could also have used a global variable, but good programming practice strongly suggest AGAINST global variables for many good reasons.  They should be used only when there are no other alternatives.

So we have a module variable to hold the reference to the Auction Form.  All that is left is to instanciate the form.  This could be done in the onclick event itself but I rather use a custom GET property (anAuctionForm) to take care of such things.  So if you want to open the Auction event on, lets say the double-click event on the control, you can refer to the same property to obtain the instance.  Otherwise, you will need to first test the content of the variable (in case it has already been instanciated) and if not instanciated it, every where you want to use it.


Basically, it all drills down to Object Oriented and Event Programing design.


I hope I did not confuse you more than you were...

I forgot to explain the Get Property:

The first part is simple.  If the variable holds nothing, then it need to be instanciated:

   If moFrm Is Nothing Then
      Set moFrm = New Form_AuctionForm
   End If
   

But if it has been already used, it is possible that the user have close it use File Close menu.  So the reference will be invalide.  If you try to get a property from an invalid reference (moFrm.name) then VBA will generate an error.  If there is an error, we simply have to create a new instance of the form.

   On Error Resume Next
   x = moFrm.Name
   If Err.Number > 0 Then
      'form has been closed
      Set moFrm = New Form_AuctionForm
   End If

Again, no more than I already was. :)

Say, if there is no auction yet associated with a given order -- that is to say, when looking at the OrderForm there is no auction in the subform -- and the command button is clicked the following error occurs:

"Run-time error '94': Invalid use of Null."

The debugger highlights the command button's OnClick event as the place where the error occurs. Is there a simple place to avoid this problem, perhaps so that if the button is clicked when there are no auctions in the subform then nothing happens?

In the onclick event code:

Private Sub ViewAuction2_Click()

   dim oF as Form_AuctionForm

   if isnumeric(Me.[OrderLots Subform].Form!EbayNum.value ) then
       set oF = anAuctionForm
       oF.GotoAuction Me.[OrderLots Subform].Form!EbayNum  
   end if

End Sub

Got it. One more thing...

Earlier you said, "On the Auction form, if you have a button to close the form, instead of closing, you can set the visible property to false.  This will improve your application performance but there is a caveat that I can explain if you want to go that route."

I don't know what the users' preference will be: to close the Auction Form completely or just hide or reduce the window. At this time, however, there is no "Close" button; it would be closed by using the upper-right X button.

Could you explain your caveat, in the event that I consider using a "Close" button as you describe?

From the user point of view, when he will click your "close" button, the form will disappear the same as if he clicked on the X button.  HE will see a differnece next time he click on the button to see an Auction because the form will already be loaded in memory.

Lets say you add a Cloase button named cmdClose, then the OnClick event of the code will be:


me.visible = false.


The Caveats is that the form may be open from other ways.  At a minimum, you will open it manually to test it.  If the user then click on the close button, the form will remain in memory but will not be accessible by any "ordinary" means.  Furthermore, it may happen that when you quit Access, its process iwill stay in memory.

The easy solution is this:

In the Auction form, declare a module variable (before the subs):

private mIsAutomated as boolean

This variable will be defaulted to False.

Add the indicated line in the GotoAuction method:

Public Sub GotoAuction(pEbay As Long)

   Dim rs As Recordset
   Set rs = Me.RecordsetClone
   rs.MoveFirst
   rs.Find "eBayNum = " & pEbay
   If rs.EOF Then
      MsgBox "Not Found"
   Else
      Me.Bookmark = rs.Bookmark
      Me.Visible = True
     mIsAutomated = true  '<<<<<<<<
   End If

End Sub


Then the close button OnClick event code will be:

if mIsAutomated  then
   me.visible = false
else
   docmd.close acForm, me.name
end if


So if the form was not open through programming, then it will completely close.

This is the general idea.  Of course there are no limits to the error validation and robustness you may want to add.  But I think this is the minimum.

You say the OnClose event would be:

if mIsAutomated  then
   me.visible = false
else
   docmd.close acForm, me.name
end if

Question: "if mIsAutomated"........ If it what? Is this just a way to ask "If mIsAutomated exists", that is, "Is it True"?

mIsAutomated is defined as a boolean, so it can only contain 2 values:  True or False.  When declare, a boolean automatically take the value False.  

The expression that we typed after the IF verb has to evaluate to a boolean (e.g. True or False)

So

IF mIsAutomated then

is the same as

IF mIsAutomated = true then




Some people will tell you that the second form is preferable.  This, in my opinion, date back to the days we did not have an explicit boolean expression.  Back then, an expression was consider true if it was different to 0  and false if it was equal to 0.  Then I understand that it was better to explicitely test the values, but with boolean variable, I found it redundant.

Okay, that makes sense.

Ragoran, thanks for all of your help and your patience. Not to mention your timely responses. On other threads this conversation would have taken months! I'm increasing the point value on this one since it was more involved than I anticipated -- plus you provided explicit code so I didn't have to sit around guessing at syntax, etc. I REALLY appreciate that!

Thank you for the points.  My biggest award is to have help someone who appreciated it.

Bye

Whoops! Ragoran, before we end this...

I am trying to adapt this solution to another similar situation. This time I've got a command button on the AuctionForm that needs to open the form LotForm to the record corresponding to a value on the AuctionForm called LotNum.

The difference between this situation and the other one is that the value being used to determine what record to open up in LotForm is actually a value on the main AuctionForm, and is not inside of a subform. So I changed the button's OnClick event to:

Private Sub ViewLot_Click()
   Dim oF As Form_LOTform
   If IsNumeric(Me.Form!LotNum.Value) Then
       Set oF = anLotForm
       oF.GotoLot Me.Form!LotNum
   End If
End Sub

When I click the button, NOTHING happens. I have put all the other code from your solution where it needs to go, and modified it with the proper form names, etc.

Since there is no subform involved in this scenario, I thought the only thing that needed to be different (aside from changing all the form names in the code) was that the OnClick event simply needed to know where to look. Is my syntax wrong?

Since the control is on the same form try

me.LotNum

Instead of

Me.Form!LotNum


You can also put a breakpoint on the code to see where it goes.  


Is LotNum a number ?

Hey I think I know what the problem is!  The OnClick event involves the Boolean test IsNumeric..... and LotNum values are ALPHA-NUMERIC, not just numeric. What would be the proper Boolean test for that?

The test should be

if len(nz(me.LotNum,"")) > o then


nz( ) is a function that test the first parameter.  If it is null,then it returns the second one, else it returns the tested value.

len( ) return the lenght of a string.

You want to move only if LotNum is not empty, so the lenght will be more than 0.


You will also need to modify the GotoLot method:  the parameter will be string, not long; and you have to add " around the value to search on the rs.find line.  Look at this example but don't copy paste because I don't know the actual name of the field to search into.


Public Sub GotoLot(pLot As string)

   Dim rs As Recordset
   Set rs = Me.RecordsetClone
   rs.MoveFirst
   rs.Find "LotNum = """ & pLot & """"
   If rs.EOF Then
      MsgBox "Not Found"
   Else
      Me.Bookmark = rs.Bookmark
      Me.Visible = True
     mIsAutomated = true  
   End If

End Sub

I am off for the (long) weekend.

I will answer your question next week.

Bye

Hmmm... I'm getting the "Not Found" error window every time I click the button; presumably it's the error trap built into the GotoLot method.

*****Here's the OnClick() event:

Private Sub ViewLot_Click()
   Dim oF As Form_LOTform
   If Len(Nz(Me.LotNum, "")) > 0 Then
       Set oF = anLotForm
       oF.GotoLot Me.LotNum
   End If
End SUb

*****Here's the GotoLot method:

Public Sub GotoLot(pNum As String)
   Dim rs As Recordset
   Set rs = Me.RecordsetClone
   rs.MoveFirst
   rs.Find "LotNum = """ & pNum & """"
   If rs.EOF Then
      MsgBox "Not Found"
   Else
      Me.Bookmark = rs.Bookmark
      Me.Visible = True
   End If
End Sub

The MsgBox is only invoked in an End Of File situation.... I'm confused again... :)
Also, when/where is pNum being assigned an actual value?

When a Find does not work, the recordset is position at the enf of file.  This is why we test this property to determine if the search di find a record or not.  You could also use rs.nomatch for this purpose.

pNum is declared as a parameter to the GotoLot method.  When you call this method from the OnClick event, you added Me.LotNum after its name.  This is the value you want the parameter to have.

There could be many reason to explain why the search is not working.  Obviously, I am assuming that your are searching for existing value.

Are you formatting the LotNum value (add or remove dashes, slashes, prefix, etc). ?

Have you try to put breakpoint to see what are the values of the variables as the code executes ?

The value of Me.LotNum is an alpha-numeric value that already exists. It may or may not have a dash in it.

My point was more that if the value n the table is:

1234-ab/3

Then you have to search for exactly that.  If you use input masks, then the value in the table may be different than what you see on screen

Another example will be leading zeros.  If your ID is like 00123 and you translate it to numerical because of formatting in a text box for exemple, then the value becomes simply 123.  If you send it to the GotoLot method, it will search for "123", not "00123" which from a DB point of view is different.

Have you try putting breakpoints to see what happens as the code executes ?

Yes.

The code runs through the GotoLot method just fine, except for the fact that rs.EOF always winds up being TRUE.  I was able to verify that the value of pNum was being properly determined. The value of LotNum being passed from the ClickEvent to GotoLot method has no Input Masks or anything on it. The value of LotNum is derived as follows:

The value is initially entered into the LOT table (LOT.LotNum).
LOT.LotNum is bound to a textbox called LotNum on the Access form LotForm.
LOT.LotNum is also replicated into the AUCTION table as a foreign key (AUCTION.LotNum).
AUCTION.LotNum is bound to a textbox called LotNum on the Access form AuctionForm.
The LotNum textboc on the AuctionForm is referred to in the ClickEvent as Me.LotNum.

So the LotNum being looked for in GotoLot shouldn't differ at all from the LotNum textbox on the LotForm.

Good work Sherlock ;-)

Change the GotoLot method as follow:

Public Sub GotoLot(pNum As String)
   Dim rs As Recordset
   dim wCriteria as string
   Set rs = Me.RecordsetClone
   rs.MoveFirst
   wCriteria = "LotNum = """ & pNum & """"
   rs.Find wCriteria
   If rs.EOF Then
      MsgBox "Not Found"
   Else
      Me.Bookmark = rs.Bookmark
      Me.Visible = True
   End If
End Sub


You can then break on the rs.find method and look into the variable wCriteria to see when it contains.  Yan can copy this string, create a new query (in sql window) as (replacing <SrcQuery> with the name of the query or table the Lot Form is bound to)

Select * from <SrcQuery> where <paste criteria here>


You can then run this query to see if it return the result ar gave you an error.

I used a msgbox as a break so that the value of wCriteria gets displayed while the code is running. In my example, wCriteria was said to have the value      LotNum = "FR-123"
which would be correct.

I then ran the following query in SQL:

Select *
From LOT
Where LotNum = "FR-123"

It was automatically formatted as follows:

SELECT     *
FROM         dbo.LOT
WHERE     (LotNum = [FR-123])

And it produced the following error result:

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'FR-123'

I forgot that you were using SQL server.  There are few synthax differences between the SQL from Access and SQL Server.  One of them is that strings need to be delimited with single quotes ( ' ) instead of double quotes ( " ) .

replace the line (in GotoLot sub):

wCriteria = "LotNum = """ & pNum & """"


with

wCriteria = "LotNum = '" & pNum & "'"


and try again.

Woo hoo!  I had forgotten that, too.  NOW it works.  Thanks a bunch!

Is there a way I can get you more points?  :)

Typically, this is not allowed.  But the EE rules state that if someone asked a "long" followup question to an already PAQ'ed one, then it is tolerated.

Post a new question title "Points for Ragoran".

In the body of the question, explained that you feel I deserve those as a followup of another question.  Add a link to this question (copy paste the URL of this page in the description of the new question)

With that information, the moderator should accept the "Points for" question.


Thanks and Good luck with the rest of your project.

Thanks again. You've been a big help.