Using DoCMD.FindRecord getting runtime error 2162

I have a form on which there is a combo box, which gets its data from a number field in a table, which has been sorted distinctly into descending order. What I want to be able to do is if the user changes the combo box number to something else then it moves to that record.

The code I wrote looks like this:

Private Sub Shipment_Number_Change()
  Dim ShipmentNumber As Integer
    [Shipment Number].SetFocus
    ShipmentNumber = [Shipment Number].Value
    Debug.Print ShipmentNumber
    DoCmd.FindRecord ([ShipmentNumber])
End Sub

I put the debug.print in there so I could check that the code correctly gets the right number (which it does), but because the control is not bound to anything I believe this is my problem.

So, here's my problem. I want to get distinct values into a combo box (I have already managed this bit) from a table, and if the user changes the combo box, find the first occurance of that record in a table called ChillOrders.

I am somewhat mystified why I cannot bind the combo box to the table (the field is called "ShipmentNumber"), and having written this sort of thing many times before in MSSQL I can't see why this cannot simply be done.

Anyone got any suggestions?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Perhaps you should again use the .Value property, e.g.:
    DoCmd.FindRecord [ShipmentNumber].Value

Another good solution would be to create a new combo, but with the magic wand depressed. In the list of wizards, select "Find a record on my form ...", follow the steps, and then study the resulting VB. It works quite well, and uses a simpler method.

Good luck!

> but because the control is not bound to anything I believe this is my problem.

No, you do not want a bound combobox!!!   That would cause the cbo to change the shipment number in the current record, not locate a new record.

First suggestion is to use After_update event instead of change event.

>I am somewhat mystified why I cannot bind the combo box to the table (the field is called "ShipmentNumber"...

As I said before, you don't want to bind the combo box.  But the fact that you cannot, suggests that the form is not bound to the ChillOrders table.  

Second suggestion:
Bind your form (form's recordsource property) to the ChillOrders table or a query that returns the chillorders table.

>What I want to be able to do is if the user changes the combo box number to something else then it moves...
What moves?   Assuming you want the current record of the form to move, read on.

Fourth suggestion, alternative 1:
Add a text box and bind it to shipmentnumber.  You bind it by choosing the shipmentnumber column in the control source property. Probably, it should be either disabled,  locked, or hidden so that one cannot change the shipment number in that text box.

Private Sub Shipment_Number_AfterUpdate()
    txtBoxShipmentNumber.SetFocus                'this is the texbox just added
    DoCmd.FindRecord ([Shipment Number])
 End Sub

Fourth suggestion, alternative 2:
Limit the form to a single record.  To do that, the form's record source must be a query with a where clause like:
  "where shipmentnumbercolumn=[Shipment Number].   Then the form will be bound to a single record.  

Private Sub Shipment_Number_AfterUpdate()
End Sub

Try this one. There are some revision on the variable and objects subfunction.
Tell me if it works. Good Luck!

Private Sub Shipment_Number_AfterUpdate()
  Dim vShipmentNumber As Integer

    vShipmentNumber = Shipment_Number.Value
    Debug.Print vShipmentNumber
    DoCmd.FindRecord vShipmentNumber
End Sub
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

zaphod_beeblebroxAuthor Commented:
Thanks guys for your responses. My Access is somewhat rusty and I realised today why it wouldn't work. The only thing on the form when I was trying it was the combo box itself (doh!) so of course it would not work.

I have used the wizard to create the form completely and it now works as I wanted. However, I have a subsequent question.

Why can I not see  the "Find a record on my form ..." if I access more than one table on the form? Works great when I only have one table, but never appears when I add data from other tables. I tried using a query as one site suggested that worked, but of course I could not update the records.

Is there a way to make a query updatable or how can I use the "Find A Record on my Form" option if I access more than one table? Do i need to bind it to a single record, such as dqmq suggested above? The only table that I want to update is the Orders table the others are reference tables (such as Customer Names and addresses) which should be read only on the form (I deal with updating those elsewhere).

Any suggestions appreciated.

That would be a general database question...

In order for a multi-table/query query to be updatable you need:
* each separate table/query to be so
* links only made on raw key fields (no calculations)
* no many-to-many relationships

Let's say your table tblCustomers uses CustomerID as primary key field (the little key symbol). This implies that it has a unique index and that this field is required. This is automatic when using  an autonumber, and inforced at data entry time if not.

Your table tblOrders has also a field CustomerID, but this time with a simple index allowing duplicates. This is caled a "foreign key", because it relates to the key of another table. Orders themselves are identified by an OrderID or OrderNumber primary key field.

You should create the relationship in the relationships window and choose "enforce referential integrity".

When you then join these tables in a query, the result will be updatable. In simplified form:

    SELECT * FROM tblOrders LEFT JOIN tblCustomers
    ON tblOrders.CustomerID = tblCustomers.CustomerID;

The command button wizard will also be happy to use OrderNumber as the "primary key" of this query, because that number alone uniquely identifies a row of the result. If that field is missing from the query, or if the query uses other tables resulting in a many-to-many relationship, it no longer works.

I hope this clarifies, good luck!
zaphod_beeblebroxAuthor Commented:
Thanks Harfang, after a bit more work I've figured out that my problem is nothing to do with my query. I used the wizard to import all fields from all tables I was dealing with and then told it to create the form.

I was unable to make any changes to any fields. If however, I only import a single table then it works great. What's going on here? I cannot believe something so simple as editing data from one table and displaying related data from another is so frustratingly difficult to do.

Can this be done? If So HOW???????
What can I say?

Select a table, choose "Insert / Autoform". You can edit fields in this form.

Create a query based on two tables, observe the link between them, as you have set up in the relationships window. Select all needed fields, including the key fields (IMPORTANT).
Save query, verify that you can edit data...
Close, select in database window, choose "Insert / Autoform". Everything  that was possible in the query is possible in that new form... you can edit data.

It is really surprisingly simple if you try the simple things first. Sadly, wizards are not part of the simple things!

Good luck!
zaphod_beeblebroxAuthor Commented:
Thanks Harfang I did all this. When I create the query it still stubbornly sits at read only.

I imported the tables from an Approach database (the users wanted it updating), so there are no key fields (do I need these -- is this the problem?). I have converting the application from Approach 97 to Access 2003 trying to keep more or less everything as it was.

I have figured a work around and thats to use a sub form for the data from the other tables. Appears to do exactly what I want (so far), any thoughts?
zaphod_beeblebroxAuthor Commented:
Just further information. I created key fields in each table and re did the query. Still the same problem. I created a query on a single table, works fine I can update the table. What's preventing me from creating a query that opens one or more related tables and allows me to modify any data in any field??

> (do I need these -- is this the problem?)

Yes, you need key fields. For single-table stuff, Access doesn't need them, but if you want to create editable multi-table queries, key fields are essential.

* table Customers has a primary (unique) index on the field CustomerID
* table Orders has a field CustomerID. This can be indexed for better performance, allowing duplicates.
* create a query on those tables
* use as link (and as only link) the one from Customers.CustomerID to Orders.CustomerID

The query is updatable.

Perhaps the problem is this: you do not only need key fields (or at least unique indexes), you also need to use these for the relationships. If you can't get it to work, simplify your tables to the bare minimum while still showing the problem, post here the table structures (field names, field types, indexes and keys), along with the query that results in a read-only datasheet (switch to SQL view, copy and paste here).

Hope this helps,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zaphod_beeblebroxAuthor Commented:
Harfang, you is the man, that was it. I added AutoFields to each database and made them the primary. The query works exactly as expected. Thanks a lot.
Glad you worked it out, and thanks for all the fish!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.