Subform Data Entry Button - Search Button

[thaBadfish]

I have a form [Search Results] that is set to pull entries from table [Drawings] in a continuous form. I added a button to call up a data entry form [frmDrawingDataEntry] which is linked to each table entry via [ID]. I have a tempvar which links the data entry form to the search results form via ID, and each button brings up the correct corresponding data entry form, where I can edit fields, save, move to previous or next entries, or create a new entry.

The issue occurs after I bring [Search Results] into a new form [Drawing Search] as a subform. [Drawing Search] displays [Search Results] correctly, including each button (which also correctly brings up the corresponding data entry form). However, the point of this form is the keyword search feature that I included to filter the subform [Search Results]. The search feature consists of a text box "Keywords" and a button "SearchButton". The button has an on click event which evokes VBA as such:

Option Compare Database
Option Explicit


Private Sub SearchButton_Click()
    Dim SQL As String
    
    SQL = "SELECT [Drawings].[Project Description], [Drawings].[Drawing Author], [Drawings].[Drawing Number], [Drawings].[Drawing Name], [Drawings].[Drawing Date], [Drawings].[Storage Location] FROM [Drawings] WHERE [Project Description] LIKE '*" & Me.Keywords & "*' OR [Drawing Author] LIKE '*" & Me.Keywords & "*' OR [Drawing Number] LIKE '*" & Me.Keywords & "*' OR [Drawing Name] LIKE '*" & Me.Keywords & "*' OR [Drawing Date] LIKE '*" & Me.Keywords & "*' OR [Storage Location] LIKE '*" & Me.Keywords & "*' ORDER BY [Drawings].[Project Description]; "
    
    Me.SearchResults.Form.RecordSource = SQL
    Me.SearchResults.Form.Requery
    


End Sub

[SearchResults] is the subform on the main form [Drawing Search] with a source object of the form [Search Results] (I probably could've named those in a less confusing manner). So if you followed me so far, I have a search feature which requeries a subform on a main form. The search feature displays the correct results, however once I click the button which calls up the data entry form (mentioned previously) I get a "Type Mismatch" error referring to the macro I have set up for assigning a tempvar linking the data entry form to the search results via [ID]. Basically, the data entry form has a on load event which states:

If Not IsNull([TempVars]![VarDrawingID]) Then
     SearchForRecord
          Object Type: Form
          Object Name: frmDrawingDataEntry
          Record: First
          Where Condition = ="[ID]=" & [TempVars]![VarDrawingID]
End If

The subform button looks like this:

SetTempVar
     Name: VarDrawingID
     Expression =[Forms]![Drawing Search]![SearchResults].[Form]![ID]

OpenForm
     Form Name: frmDrawingDataEntry
     View: Form
     Filter Name:
     Where Condition:
     Data Mode: Edit
     Window Mode: Dialog

So once again, this button brings up the correct [frmDrawingDataEntry] until I search via the VBA I referenced earlier, which gives me the "Type Mismatch" error.

I'm very new to access, and I'm just trying to create a simple, clean database that will allow my client to quickly search his drawing records and find the storage location. I have linked the file, and any help is greatly appreciated.
If you use the linked file, the main form [Drawing Search] will open. If you click the button under any entries "EDIT" field, the [frmDrawingDataEntry] form will pop up correctly. If you then type anything into the keyword search (such as "CANCER") the results will correctly filter, but the "EDIT" button will no longer work.

Link to file: (733KB)
https://onedrive.live.com/redir?resi...t=file%2caccdb



Thanks

[pbaldy]

Unhide the ID field (which the edit button relies on) and perform your search.

[thaBadfish]

Ok, that gives some more insight into what's going wrong, but I'm still unsure how to fix it.
So with the ID field visible in the subform, I see that once I search the ID fields change to "#Name?". I'm not yet sure why, but the ID field breaks once the VBA search occurs.

[pbaldy]

Because the ID field isn't here:

SQL = "SELECT [Drawings].[Project Description], [Drawings].[Drawing Author], [Drawings].[Drawing Number], [Drawings].[Drawing Name], [Drawings].[Drawing Date], [Drawings].[Storage Location]

[thaBadfish]

Damnit, why'd it have to be such a simple solution? Works great now.

Much appreciated!

[pbaldy]

Happy to help, and welcome to the site by the way!

[thaBadfish]

So I got everything working, and added my last requirement - an Add New Drawing button - and now the search feature no longer works! The subform only shows one result. For the life of me, I cannot figure out why. Can anyone take a look at the linked file and take a look?

https://onedrive.live.com/redir?resi...t=file%2caccdb

[pbaldy]

You gave the main form a record source and master child links keep the subform from displaying anything except records linked to the hidden record on the main form.

[thaBadfish]

I can remove both Link Master Fields and Link Child Fields, which momentarily restores all results, but clicking search just hides them all except for one. I'm not sure how to resolve.

[pbaldy]

I deleted those plus the record source of the main form.

[thaBadfish]

If I delete the record source of the main form [Drawing Search], then it asks for ID and still only shows one record.

**SORRY**

I stand corrected, I hadn't deleted all 3 fields, only the record source of main form. Once I went back and "re"deleted the master and child links all was back to normal.

Can't thank you enough.

[pbaldy]

No problemo! Basically the main form is a container for your search functions and the subform, so it doesn't need (and shouldn't have) a record source.