Open form displaying data based on value of textbox on current form

[Mattbro]

Hello all,
It's embarrassing, but I've been searching for weeks and cannot solve!
Access 2003. I've a form: "Xpresearch". It has a textbox: Text3. In this, I scan the serial number of the product e.g. HH01234X/12. The serial number is the primary key in the table "Sheet 1". Using the event properties of Text3, I want to code, to open a form "Xupdate" which contains textboxes displaying the record relating to the relative product. I have bound Text3 to Sheet1.Serial Number and am playing with the code;

Private Sub Text3_AfterUpdate()
DoCmd.OpenForm "Xupdate"
DoCmd.GoToRecord acDataForm, "[Serial Number] = ' " & Me.[Text3] & " ' "
End Sub

....and lots of similar variations. This one won't work, and gives runtime error 2481 'The object [Serial Number]=' HH01234X/12' ' isn't open'
Can anyone see what I am doing wrong?
Many thanks,
Mattbro

[Bob Fitz]

Perhaps:

DoCmd.OpenForm "Xupdate",,,"[Serial Number] = '" & Me.[Text3] & "'"

[Mattbro]

Hmm-I have given it a try....I now have a warning that it cannot proceed as I am trying to create a duplicate value in the primary key [Serial Number]. I am reckoning that's why the GoToRecord command may be more appropriate...unless it too would try to duplicate the value.....

[ItsMe]

Hmm-I have given it a try....I now have a warning that it cannot proceed as I am trying to create a duplicate value in the primary key [Serial Number]. I am reckoning that's why the GoToRecord command may be more appropriate...unless it too would try to duplicate the value.....

The duplicate value is in your current recordset. Try saving the record before using the code in post #2.

Saving the record or setting focus on another field will cause the error

[Bob Fitz]

Hmm-I have given it a try....I now have a warning that it cannot proceed as I am trying to create a duplicate value in the primary key [Serial Number]. I am reckoning that's why the GoToRecord command may be more appropriate...unless it too would try to duplicate the value.....

But the code I posted only opens a form called Xupdate and shows the record where"Serial Number" = the value in Me.Text3

Do you have any code in the forms On open event.

[ssanfu]

DoCmd.GoToRecord acDataForm, "[Serial Number] = ' " & Me.[Text3] & " ' "

The syntax is wrong.
The actual syntax of the statement is:

DoCmd.GoToRecord [objecttype, objectname][, record][, offset]

Nowhere is there an option to select a record.

I am reckoning that's why the GoToRecord command may be more appropriate

Nope..... won't work.


Bob's example should work.

[Mattbro]

Hi,
The Xupdate form loads at the correct record, but immediately, I get a pop-up message "The changes you requested to the table were not successful because they would create duplicate values in the primary key or relationship etc".
There is no code in the Xupdate forms On Open Event.
'ItsMe', I am not sure what you mean. The Xupdate form is bound to Sheet 1. I am guessing that by opening at a record, it is assuming I want to save that record in addition to the duplicate in Sheet 1, whereas what I want to do is amend the details in sheet one-not create a duplicate.
This is on the cusp of working......any suggestions are very welcome.

[ItsMe]

I am guessing that by opening at a record, it is assuming I want to save that record in addition to the duplicate in Sheet 1......any suggestions are very welcome.

It seems to me that Xpresearch is creating the duplicate key. There is nothing in the code Bob posted that is appending a record. It must be in the form that you are leaving. You mentioned that there is not any code that runs on Xupdate's load event. It also seems that you are really focused on Xupdate.

Just check out the original form you are on. Instead of running the original code, try setting focus somewhere. Move the curser out of field3. Try Me.focus. That will set focus on the form Xpsearch and force an update to the current form.


Private Sub Text3_AfterUpdate()

Me.setfocus

End Sub

[Mattbro]

You may be right. The trouble is, the code causes the forms to load. If Xpresearch Text3 doesn't code to open the Xupdate form, then there will be no update.
I am wondering if it is due to having the controls bound. Text3 is bound to Serial numbers in Sheet 1. If I remove it, it seems to work. I'll experiment with this further and see. Does anyone know if this would solve the issue?

[ItsMe]

Did you try the code I provided in post # 8 or are you just speculating?

[Mattbro]

Well, I couldn't see how to use it instead of the existing code. I have added it as well as the existing code, but to tell the truth, having experimented with removing the bound factor on Text3, the system works. I guess the bound factor was trying to write to Sheet1, thus trying to create the duplicate. So you were absolutely correct, thanks! The problem was with Xpresearch and not Xupdate

Did you try the code I provided in post # 8 or are you just speculating?

Thanks to all.
Mattbro.