Subform With querry info On Mainform showing #Error

[Oculus82]

I think were getting wires crossed


The search forms on the welcome screen are a good idea but its the quick search i'm trying to get working how i intend on using it

Using the database you have (7.06ss)
On the Welcome form - Enter SHPCMO01 into the quick search (it displays the location/zone/pod and desk information.)
Without closing that form now open the "**BlankForm** - (The quick search is back to default and empty) - i want it to have what i just typed into the previous form (from any new forms i open, so if i open avantia the info is there.. if i open media room from avantia - the same info is there)

I don't understand this.
You had 6 forms almost identical: when you clicked on the button "Asset Search", a tabbed form for "Asset Search" opened; when you clicked on the button "Desk Search", another tabbed form opened; same for the other two search forms. The button "Edit Asset" is another tabbed form as is "New Asset". I just moved the pertinent controls to tabs in a tab control. Makes more sense to have 1 form with tabs than to have 6 forms.

Next you had the DLookup function as the control source for the controls on the search forms AND you had the search forms bound to a table.
Using the Dlookups is not the right way to go. Since the form is bound to a table (actually, I used a query), each of the search form buttons filters the record set to the record being searched for. If you do the search on the "Asset Search" tab and a record is found, all other tabs reflect that record.

There were only 3 records in the dB, so I changed the 3rd record (Asset Number "SHPCMO03") model to "Pro Three 600" to distinguish it from the other two records.
On the "Asset Search" tab, enter "SHPCMO01" and click the search button. The model should be "Pro One 600".
Enter "SHPCMO03" and click the search button. The model should now be "Pro Three 600".
Click on the "Switch Port Search" tab - the Model should still be "Pro Three 600".

I shouldn't have left the "Quick search" as a sub form - those controls should be on the "Welcome" form (in MY version of the dB).


Now go to the "Quick Search" and enter "SHPCMO01". Notice that the model data in the "Switch Port Search" tab changed from "SHPCMO03" to "SHPCMO01".

Thought that is what you wanted.... but that is easy to remove.......
Comment out or delete the two lines in GREEN

Private Sub SearchBox_Exit(Cancel As Integer)
    Dim r As DAO.Recordset
    Dim txSearchBox As String
    Dim sSQL As String


    If Len(Trim(Me.sEARCHbOX & "")) > 0 Then
        sSQL = "SELECT InfoSheet.Location, InfoSheet.ZoneColor, InfoSheet.Pod, InfoSheet.Desk"
        sSQL = sSQL & " FROM [tblAssets] AS InfoSheet"
        sSQL = sSQL & " WHERE InfoSheet.[AssetNumber] = '" & Me.sEARCHbOX & "';"

        Set r = CurrentDb.OpenRecordset(sSQL)
        If Not r.BOF And Not r.EOF Then
            Me.Location = r("Location")
            Me.ZoneColor = r("ZoneColor")
            Me.Pod = r("Pod")
            Me.Desk = r("Desk")
          '    Me.Parent.Filter = "AssetNumber = '" & Me.sEARCHbOX & "'"
          '    Me.Parent.FilterOn = True
        Else
            Me.Location = "Asset unknown"
            Me.ZoneColor = "Asset unknown"
            Me.Pod = "Asset unknown"
            Me.Desk = "Asset unknown"
        End If
        r.Close
        Set r = Nothing
    Else
        Me.Location = vbNullString
        Me.ZoneColor = vbNullString
        Me.Pod = vbNullString
        Me.Desk = vbNullString
    End If

End Sub

And then you said:

So I tweaked it........ and now you don't like it?


Doesn't get cleared in the db I just tested..... but maybe I misunderstood again.......


----------------------------------------------------------------------------------------------
The dB I have does not have the forms with maps. If you add an Asset number in the quick search, then click on the "Avantai" button, a map opens. Using your images, do you want something highlighted to show the Pod/Desk? (Pod 2, desk "E"?) would take a lot of code for all of the buttons, but it is doable....

[ssanfu]

I think this

I think were getting wires crossed

is your polite way of saying "YOU DUMMY! Why can't you read my mind???" (FYI, my crystal ball is in the shop for calibration)


OK, let me try again. Even though the campaign buttons and map forms are blank (in the dB I have), every one of the 35 forms will/should have this layout on the right side of the form? (ignore the red line and oval)

So from any form you want to be able to click on a different button and still have the same asset number appear in the new form? And be able to (quick) search for a different Asset number?

Only one form will be open/visible at a time?
So you open the dB and the "Welcome" form appears. You enter "SHPCMO03" in the quick search control, then click on the "Boardroom" button. The "Welcome" form closes and the "Boardroom" form opens...WITH the data for "SHPCMO03" in the quick search controls?? Then you click on the Training Room 3 button and "Boardroom" form closes and the "Training Room" form opens???


Edit: And do you HAVE to have tabbed forms? Could it be overlapping windows?

[Oculus82]

@ crystal ball (i wouldn't be so insulting as to call you a dummy after all your hard work so far )

Edit : OK, let me try again. Even though the campaign buttons and map forms are blank (in the dB I have), every one of the 35 forms will/should have this layout on the right side of the form? (ignore the red line and oval)
Yes this is correct - all the forms are identical (the oval and red line was purely for example purposes and was my search criteria in quick search that points to the location/computer in question)


Almost 100% correct yes..
So you open the dB and the "Welcome" form appears. You enter "SHPCMO03" in the quick search control, then click on the "Boardroom" button (or any button as the quick search is on all forms). The "Welcome" form closes and the "Boardroom" form opens...WITH the data for "SHPCMO03" in the quick search controls?? (Spot on.. data in the quick search is shown on every new form opened, that's why i though the sub form would be a good idea)

Then you click on the Training Room 3 button (or any other form button) and "Boardroom" form closes and the "Training Room" form opens??? - With the Quick search data originally entered "SHPCMO03"


Edit: And do you HAVE to have tabbed forms? Could it be overlapping windows?

Tabbed forms are preferred as the borders/boundary I've set cannot change, the database once opened is a perfect fit for the laptops we use (1080 resolution) without messing about scrolling up/down/left/right or dragging windows about.
It just opens and you can navigate all areas. Because i'm mobile all the time i don't use a mouse and have to rely on the track pad which is a pain.. im going for simple point and click controls

I think this

is your polite way of saying "YOU DUMMY! Why can't you read my mind???" (FYI, my crystal ball is in the shop for calibration)


OK, let me try again. Even though the campaign buttons and map forms are blank (in the dB I have), every one of the 35 forms will/should have this layout on the right side of the form? (ignore the red line and oval)

So from any form you want to be able to click on a different button and still have the same asset number appear in the new form? And be able to (quick) search for a different Asset number?

Only one form will be open/visible at a time?
So you open the dB and the "Welcome" form appears. You enter "SHPCMO03" in the quick search control, then click on the "Boardroom" button. The "Welcome" form closes and the "Boardroom" form opens...WITH the data for "SHPCMO03" in the quick search controls?? Then you click on the Training Room 3 button and "Boardroom" form closes and the "Training Room" form opens???


Edit: And do you HAVE to have tabbed forms? Could it be overlapping windows?