You can't go to the specified record

[Compressor]

Well.... I can't

Structure of DB and relations is included in jpg.

I have created two separate parts for sales. One which is used for over the counter sales, and one which is used for sales and labour per case.

I've created two forms (a form with a subform) for the bottom ones. Whenever I am on the main form, I select a customer and click Add Sale I can just keep on adding sales to the customer without problems.

I've copied the two forms and renamed them to be used again for the tables in the top of the structure/image. I've changed the Data Source of the Forms of course to the correct ones. On the case info form (which in this case acts the same as the Mainform for the other situation) I have placed a button to be able to add a new sale to the case. Using the code:

    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenForm "CaseTotalProductsSaleAdd", acNormal, "", "", , acNormal
    DoCmd.GoToRecord , , acNewRec

which is the same as in the other situation, but of course the reference was changed to the new form as you can see. That still presented the same problem, so I build them from scratch.

At first I had based the forms on queries which matched with their corresponding clients/cases but I have removed that base temporarily and linked the forms to the tables directly since I started to doubt myself and started to think I might have mesed up on that end.

When the table is empty I can click the Add New sale button and the correct form with its subform appears. The value in the textbox SaleID is set to (AutoNumber), the value in the box TechID is set to the correct TechID number, I use =[Forms]![TechAdd]![TechID] for this. I can add one sale. Then the fun is over. Whenever I then try to add or view a sale I get the error message "You can't go to the specified record".

I really don't understand why this could be happening. I mean... I can add multiple calls to a client, multiple appointments to a call, multiple techcases to an appointment. So why can't I add multiple sales to a techcase?

This was the problem I was working on before the crash yesterday. And I've trying to find out why this happens for a pretty long time now, but without succes.

Can anyone help me please? Why can I keep adding and viewing sales in the bottom structure and NOT in the top structure while I am using the same structure, setup and code?

 

[MarkK]

I wouldn't split your case and counter sales in seperate tables as indicated by your .jpg. In your table CaseTotalProd... (and I can't see the rest), have a techID which links your case data (existing) and a clientID (which allows you to record counter sales into the same table). Dependent tables can then be unified also.
It is a very rare case that you should support two practically identical data structures when they differ only by type. In your case, if techID = 0 the item is a counter sale. If ClientID = 0, the item is services case or whatever.
I expect your record addition problem comes from the fact that you're enforcing referential integrity in your table relationships, and the new record command fails because it can't relate the new record to a record in the primary related table. You can alleviate this problem by setting the default value of the foreign key field to a value that exists in the primary table.

  Const fn as string = "CaseTotalProductsSaleAdd" 
  DoCmd.RunCommand acCmdSaveRecord
  DoCmd.OpenForm fn, acNormal, "", "", , acNormal
  Forms(fn).tbYourForeignKey.DefaultValue = [I]~id value of the linked record~[/I]
  DoCmd.GoToRecord acForm, fn, acNewRec

 

[Compressor]

Thanks for your reply.

You know what the funny thing is: there was a point at which it worked. I have started another thread in which I asked about table structures, PK's and relationships. In those tables the adding of a record was no problem.

In those tables I kept ALL values that were used before in each table, all PK's, all linked to each other with ref. integrity turned on. I set the correct values for them through a Default value = xxxxx (pointing to the values in the previous, still displayed or otherwise queried for, record/field values). That did the trick. I didn't expect to run in this kind of trouble here, since I do try and set the correct value (being TechID) to an existing ID using the default value= forms!xxxx because the form which is calling for the new sale still has the correct value of an existing TechID included in it. The form opens but doesn't seem to get a chance to set that default value to a new record. However, when I click OK and then click add new record in the standard form controls in the bottom of the form, it doesn't come up with an error anymore.

I will try your solution as soon as I get home and see what it does.

 

[Compressor]

It works!

Tnx!

 

[Compressor]

Ow, and about the splitting up of the sales: at first I only meant to have sales linked to cases, since an other situation will almost never occur. But in case they do occur, I will create a client called passer by and invoice it directly should an over the counter sale do occur. So... just a late addition so to speak.