Opening a form to view and edit information on a selected record

[david.paton]

I have a form to search through a database and on the form, a listbox with row source:



“SELECT QRY_SearchAll.WidowID, QRY_SearchAll.WidowName, QRY_SearchAll.WidowStreetAddress, QRY_SearchAll.WidowPhone, QRY_SearchAll.WidowMobile, QRY_SearchAll.WidowDOB, QRY_SearchAll.tblDivisionsTown, QRY_SearchAll.PTAbbreviation, QRY_SearchAll.AccomDescription, QRY_SearchAll.WidowNOK, QRY_SearchAll.WidowNOKPhone, QRY_SearchAll.WidowNOKMobile, QRY_SearchAll.WidowNOKStreetAddress FROM QRY_SearchAll;”


And the qry_SearchAll has the criteria “Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*"” for each field I wanted searched and it searches through fields in a database as text is typed into a text box. Before you start typing, the list box is full of every record but narrows as you type. When I locate the record I am looking for, once selected, how do I open the data entry form to edit the information?

 

[jdraw]

See this Allen Browne article and see the links at bottom to other related articles.

 

[david.paton]

See this Allen Browne article and see the links at bottom to other related articles.

I may have overlooked it but I couldn't see the answer there. I also can't code very well so I don't understand coding if the answer was written in some code. I just remember that it is possible as I have done it before but I can't remember how I did it.

 

[jdraw]

David,

I focused on this part of your post

Before you start typing, the list box is full of every record but narrows as you type. When I locate the record I am looking for

which is often called Find as you Type or similar.

Here is a link to info and example of OpenArgs that should help with selecting a record, then opening a form to manipulate the data based on your selection.

Good luck.

 

[david.paton]

The link you posted didn't really make sense but I found another link https://access-programmers.co.uk/forums/showthread.php?t=119456, which seemed to have what I was looking for but it didn't work using the line of code recommended, DoCmd.OpenForm "[FRM_SearchMulti]", acNormal, , "[Widow]![WidowID] = " & Me.SearchResults. I don't know why, as I don't know how to code. FRM_SearchMulti is the name of the search form, WidowID is the name of the PK of the records that are searched through, Widow is the table name and SearchResults is the name of the list box.

 

[david.paton]

I played around with the code and I think it is meant to be something more like this, but it still won't work.

DoCmd.OpenForm "Widow", acNormal, , "[WidowID] = " & Me.SearchResults

where widow is the name of the form that is meant to show the widow information.

 

[jdraw]

Try this link from datawright
It has format, parameters and examples

 

[david.paton]

Thanks for that suggestion, the link has this as one of the suggestions which I thought sounded like what I was looking for:


[FONT=&quot]Creating an SQL statement to display records matching the ID of the current form[/FONT]
[FONT=&quot]Often you will want to open a form and display records matching the ID of the currently open form. [/FONT]
[FONT=&quot]Private Sub cmdOpenSales_Click()[/FONT]
[FONT=&quot] Dim sWHERE As String[/FONT]

[FONT=&quot] 'Comment out or delete the line that does not apply --[/FONT]
[FONT=&quot] ''If the ID is a number, built the WHERE clause like this:[/FONT]
[FONT=&quot] sWHERE = "[CustomerID] = " & Me.CustomerID[/FONT]
[FONT=&quot] ''If the ID is a text value, you need to surround the ID in single quotes[/FONT]
[FONT=&quot] sWHERE = "[CustomerID] = '" & Me.CustomerID & "'"[/FONT]

[FONT=&quot] 'Open the form[/FONT]
[FONT=&quot] DoCmd.OpenForm "frmSales", acNormal, , sWHERE[/FONT]
[FONT=&quot]End Sub[/FONT]

I tried to adjust it to my database and I got this:

Private Sub Command36_Click()
Dim sWHERE As String

'Comment out or delete the line that does not apply --
''If the ID is a number, built the WHERE clause like this:
sWHERE = "[WidowID] = " & Me.WidowID
''If the ID is a text value, you need to surround the ID in single quotes
'sWHERE = "[CustomerID] = '" & Me.CustomerID & "'"

'Open the form
DoCmd.OpenForm "Widow", acNormal, , sWHERE

End Sub

When I try and run this I get an error message with “.WidowID” highlighted. The error message says “Method or data member not found”. Is this meant to be the PK of the main table?

 

[JHB]

..
When I try and run this I get an error message with “.WidowID” highlighted. The error message says “Method or data member not found”. Is this meant to be the PK of the main table?

It is telling you, that you don't have a control with the name = "WidowID"

 

[david.paton]

It is telling you, that you don't have a control with the name = "WidowID"

But what do I need to put in there? This is the code I currently have.

Private Sub Command36_Click()

Dim sWHERE As String

'Comment out or delete the line that does not apply --
''If the ID is a number, built the WHERE clause like this:
sWHERE = "[WidowID] = " & Me.SearchResults
''If the ID is a text value, you need to surround the ID in single quotes
'sWHERE = "[WidowID] = '" & Me.SearchResults & "'"

'Open the form
DoCmd.OpenForm "Widow", acNormal
' DoCmd.OpenForm "Widow", acNormal, , sWHERE
'DoCmd.OpenForm "Widow", acNormal, , "[WidowID] = " & Me.SearchResults

End Sub

I have been trying different lines of code and just commenting them out if they don't work.

This line of code: DoCmd.OpenForm "Widow", acNormal, , sWHERE gives me an error but if I take out the last , ,sWHERE it works but it just opens a form to view all the records but I don't know why as I don't understand the code. How do I change that line of code to open the selected record in a form view?


I tried just the code, DoCmd.OpenForm "Widow", acNormal, , "[WidowID] = " & Me.SearchResults behind the button I click but there is a syntax error and I don't know what it is. This would probably be the best option, opening just a form to view that record only but I don't know what is missing.

 

[JHB]

Replace the [WidowID] with the name of the control on the form which hold the ID you want to search for.
Else post your database, zip it, + name of the form in which you've the problem.