Search Form error

[Shipper225]

I'm hoping to get a reply to solve this. Attached is my Access 2007 database, what I was trying to do is use the AutoNumber field as a hyperlink to go from the search form to the matching Accident/Near Miss Form so that the user can update records that they just searched for. I keep getting an error saying

The table 'Accident/Near Miss' is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically.

What exactly am I doing wrong? I'd thought maybe I need to make a form that would branch between the search form and the near miss form but I am not sure how to do that.

 

[PaulO]

I wonder whether you need to CLOSE the first form down the moment you click the hyperlink, as it could be a data sharing issue?

Unfortunately I can't look at your database as it's in 2007 format.

 

[GinaWhipp]

Make the RecordSet on the Search form *Snapshot*.

 

[Shipper225]

PaulO In the macro the first step is set up to close the search form and then open the next one.

GinaWhipp, To be honest I'm not 100% sure how to make the search form a snapshot; help please?

 

[GinaWhipp]

Go to Design View of the form and open the Properties window for the form. Then go to the Data tab and where it says *RecordSource Type* change that to *Snapshot*.

 

[Shipper225]

Ok, thanks GinaWhipp I changed it and now the form closes as it should but now the link isn't opening the matching record in the form. Do you have any idea on how to fix that?

 

[GinaWhipp]

I'm not sure why not, it worked for me, of course I did just type in a line. I don't use Macros... What records is it opening?

 

[Shipper225]

I've been looking around trying to solve this and it seems that I shouldn't be using a hyperlink instead I should be using a "bookmark" to find records. I'm trying to take the autonumber (primary key) that shows up on the search form once it has been filtered down and click that to take me to that specific record in the Accident/Near Miss Form so the user can edit and update that form instead of having to click through all the records that are stored. I found this code and modified it to my database:

Private Sub AutoNumber_Click()

If Not IsNull(Me.OpenArgs) Then

With Me.RecordsetClone

.FindFirst "[AutoNumber] = " & Me.OpenArgs

If .NoMatch Then
'Display a message that we did not find the record
Msg Box "Customer " & Me. OpenArgs & " Not Found"
Else
'Make the record found the current record
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

AutoNumber being the ID field the user clicks and the field in the Accident/Near Miss Form that is being referenced.

 

[GinaWhipp]

And it works if you change the Search form RecordSet Type to Snapshot. I do not see any issue with the way you are using it versus using a *bookmark*.

 

[Shipper225]

GinaWhipp, I am getting so frustrated with this now I am really thankful you are helping me work through this bit.
In the code I posed above when I go to run it I get a "Compile Error Syntax Error" with a yellow arrow highlighting the "Private Sub AutoNumber_Click()" and the line "Msg Box "Customer " & Me. OpenArgs & " Not Found""

 

[GinaWhipp]

Upload what you have and let me take a look...

 

[Shipper225]

All test data is in here right now, the log in you can bypass for now or enter the info in the text box. You can open the search box from the Main Screen

 

[Shipper225]

Attached, had to remove the user log in forms so it was small enough to fit

 

[GinaWhipp]

Remove the space between Msg Box, it is one word MsgBox. Of course, now nothing opens and I thought you wanted the Search form to open?

 

[Shipper225]

Clicking the number is supposed to open the corresponding report in the Accident/Near Miss form

 

[GinaWhipp]

This code does not mention any form. AND since that Autonumber field can not be NULL why not just use...

Private Sub AutoNumber_Click()

DoCmd.OpenForm "Accident/Near Miss Form", , , "[AutoNumber]=" & Me![AutoNumber]

End Sub

 

[Shipper225]

See this is why you don't put someone from the shipping department in charge of making a database. It works much better thanks to all the help from you Gina, the last (as I hope at least) issue is that when I open one form then close it to go back to the search if I don't close the search form in between it says that the Accident/Near Miss Form is still open, I tried putting the End Sub after the DoCmd line you posted and it didn't work. I left the End Sub at the bottom of the Private Sub.

 

[GinaWhipp]

Hehe... we all hd to start somewhere!

I'm sorry I don't understand... tell me the order in which you open the forms and what you do so I can duplicate and see what you mean.

 

[Shipper225]

Thanks but i went the lazy way out, right before the End Sub command I put in that the search form is to close once the code is run thus forcing a fresh start for searching. Thanks for everything

 

[GinaWhipp]

No problem and good luck! We are here if you have anymore questions.