Subform With querry info On Mainform showing #Error

[Oculus82]

Can anyone who speaks plain English help me.
I've google so many times i've lost count.

Im a novice.

I have Main form called "Blank Form" - it has a custom links menu.
The idea is to copy all its contents to every form before i start populating the other forms.

On this "Blank form" i have a Quick search sub form (at least that's the idea)

So...

Ive created a Quick search form that i want to use as a sub form - this contains a search box that links to a query... I type in a value (Mixed Letters/numbers) and it displays the results under the search box in 4x boxes that DLOOKUP the query results.

This form called "Quick Search" works fine - The DLOOKUP strings are as below for each of the correctly named text boxes

1. =DLookUp("[Location]","[Quick Search Query]")
2. =DLookUp("[Zone Color]","[Quick Search Query]")
3. =DLookUp("[Pod]","[Quick Search Query]")


4. =DLookUp("[Desk]","[Quick Search Query]")

Easy.. i then have an on click/enter/exit event etc to re-query the results

So it all works so far on the Quick Search form i enter "1" say in the search box and the results are shown..

Right - I Add a sub form to the "Blank Main form" and the wizard asks me which form i want to link it to.. i Say "Quick Search" and finish the wizard but in form view everything flashes #Error
I can type into the Search box but it changes nothing.

Anyone want to try and explain this in plain understandable English?
Remember im a noob so please go easy

[ssanfu]

Welcome to the forum......

Comments in line and BLUE

Can anyone who speaks plain English help me.
I've google so many times i've lost count. Been there, done that

Im a novice. Got it

I have Main form called "Blank Form" - it has a custom links menu. So is the "Blank Form" bound or unbound? Not sure what you mean by "custom links menu"... a custom ribbon?
The idea is to copy all its contents to every form before i start populating the other forms. "Every" form must then be open before controls can be populated.

On this "Blank form" i have a Quick search sub form (at least that's the idea)

So...

Ive created a Quick search form that i want to use as a sub form - this contains a search box that links to a query... What is the control name and what is the SQL of the query?
I type in a value (Mixed Letters/numbers) and it displays the results under the search box in 4x boxes that DLOOKUP the query results. Ugh! DLookup()

This form called "Quick Search" works fine - The DLOOKUP strings are as below for each of the correctly named text boxes

1. =DLookUp("[Location]","[Quick Search Query]")
2. =DLookUp("[Zone Color]","[Quick Search Query]")
3. =DLookUp("[Pod]","[Quick Search Query]")
4. =DLookUp("[Desk]","[Quick Search Query]")
So the way you have constructed the DLookups will return the first record found. Normally, there is a WHERE clause (without the word WHERE) to search for a specific value.

Easy.. i then have an on click/enter/exit event etc to re-query the results Is this a button? What fires the events?

So it all works so far on the Quick Search form i enter "1" say in the search box and the results are shown..

Right - I Add a sub form to the "Blank Main form" and the wizard asks me which form i want to link it to.. i Say "Quick Search" and finish the wizard but in form view everything flashes #Error
I have never used that specific wizard. normally, I just drag whatever form I want to be a sub form to the main form. Never had it flash and have #Error displayed.

I can type into the Search box but it changes nothing.


It looks like the values that populate the "correctly named text boxes" are (hopefully) from the same record.
Why aren't the values grabbed from the query and "pushed" into the text boxes?

Would you post your dB so we could see what you are trying to do?


BTW, you shouldn't have spaces in object names.

[Micron]

IIRC, the wizard asked you if you wanted to link master and child fields. If you chose yes and picked one of the calculated fields for the linked child, I believe you'd get this error. Calculated fields are not updatable, and I'm pretty sure you cannot use one as the linked child field (although I believe you can for the linked master on the main form). So either one of those behaviors of calculated fields could be your issue. If that doesn't help, do post your db as suggested. If you must use a calculation for the child field, the work around is to do the calculation in your query.

[Oculus82]

Attachment 32922

The blank form is just a master form i've created that has buttons on it that link to other pages.
Its for an Computer asset Database that ties into a visio drawing of the building.

I want the same details and subform (Quick search) on every page

I said i'm a novice right
The Query looks for the asset number which cannot be duplicated in the database as there is only 1 machine with that name that's why it returns the first record found

I will replace spaces with underscores - thanks for the tip

As you can see from the database is in its infancy and pretty easy to understand.
Because its not populated yet all i'm working on is "Blank Form" - "Quick Search" and "Quick Search Query.
Records are drawn from the master information sheet

[orange]

Oculus82,

Have you tried describing in simple terms what the "business" is that you are trying to support with this proposed database?
You seem to have used some quasi-jargonese and some Access objects (forms, functions..) to tell us how you are doing something and it isn't working as expected, but we don't understand what that something is.

It looks like IT Asset Management.
Here's a free data model from Barry Williams' site that may be relevant.

If English isn't your native tongue, then write in your native tongue and use Google Translate (or other) to create the English for posting. It is certainly sufficient for communicating on the forum.

It is always better to start with a clear requirement, than to jump into a physical database and use trial and error as a design strategy.

Once readers understand your requirement in context, they may offer more focused advice.

Good luck with your project.

[Oculus82]

@orange... Its really not productive making snide comments - I have already said its a "Computer Asset Database" and its clearly written in English with maybe the odd typo

Additionally I attached the database and stated which forms i'm using but as your having difficulty in understanding my quasi-jargonese none English i'll reiterate for you... i'm using the following forms in the above attached database.

1. Blank Form (My main form i will duplicate and rename if i add new forms)
2. Quick Search (my proposed Sub Form)
3. Quick Search Query.

To clear any miss-understanding when i said "Custom links menu" in my original post - Its basically my front end that has buttons on it that point to other forms.

**Requirement**
I already have a working database (AMT1) but due to building works i have to replace and re-asset every PC in the building so i wanted to start a fresh and add a Quick Asset Search function to every Form.
The idea behind using a subform is the results will be shown on any form i open.. so i can open a form that contains a floormap and the results from the quick search will be shown without entering the asset again

[orange]

due to building works i have to replace and re-asset every PC in the building

Thanks for clarifying.
Good luck with your project.

[ssanfu]

@orange... Its really not productive making snide comments - I have already said its a "Computer Asset Database" and its clearly written in English with maybe the odd typo

Additionally I attached the database and stated which forms i'm using but as your having difficulty in understanding my quasi-jargonese none English i'll reiterate for you... i'm using the following forms in the above attached database.

I didn't see where orange made any snide comments. I had to read your posts 3 times to see where you had that it was a "Computer asset Database".
But you on the other hand, I highlighted your snippy, uncalled for comment.


The ones that try to help people with questions are all volunteers. I dare say that you probably won't hear from orange again.... at least if it was me, I wouldn't respond to you your questions due to you attitude. Everyone has missed things in posts at one point or another.


As far as your dB, there are several things that I consider problems:
You have spaces on object names.
You began an object name with a number.
You use macros - I never use macros because they are too limiting.
It looks like you have multiple forms for the same thing; the meeting rooms. Maybe they have different layouts.... but it is curious.
Your use of the DLookup function was wrong. the syntax you used resulted in a circular reference.


I renamed a few fields and a form. the "SearchBox" now returns a value.
If you enter an asset number and leave the box, the 4 text boxes will be filled with a number if found, otherwise it will have "Asset unknown".
If there is a value in the searchbox and you delete it, all boxes will be set to NULL.


Good luck.....

[Oculus82]

I apologize for that comment as i was slightly frustrated

What you have put into the VB Code i wouldn't know where to start and its this that, that i needed when i said plain English in my original post. I don't understand (yet) what
Dim r As DAO.Recordset Dim txSearchBox As String Dim sSQL As String means or the rest of it - i've been learning slowly and am no where near that standard.

I genuinely appreciate your help with this and hope i haven't offended you as you have done what i couldn't.
Is there a website you would recommend with reading material or a good book that i may learn access from that has ssanfu approval?



p.s.
Yeah - the link buttons on the side point to forms that basically have a cropped visio drawing of the room layout - each PC is given an asset as you would guess - this database helps me find PCs and details pertaining to those PCs such as Floor Ports/VLANs & Switch Port Information as well as basic details like Hardware and location.
Because the call hall and rooms are large its a pain when someone says "PC with asset SHPCGF009: has a faulty monitor" - i then have to go find SHPCGF009 like Hansel and Gretel calling the name of the person who raised the fault to find the PC.
This is version 2.0 but i have a working 1.0 that served ifs purpose, i dare bet you'd say it was crude by comparison to your work lol but the code your wrote ties into the picture ive attached.. the other rooms are just different colors.

Attachment 32957

[Oculus82]

Is there a modifications in the coding that will allow the previous search results to be followed onto the next form?

To explain myself - the contents of "Blank Form" will be copied onto every form.When viewing in form view and i type in an asset number it displays the details but when i click on one of the other form buttons the search goes to blank again rather than showing what was previously entered into the quick search

-Shaun

[ssanfu]

Sorry I've been out of touch. Client just (Fri) sent 3 mths of data (lots of errors to clean up before entering), network problems and 9 inches of snow to move....

What you have put into the VB Code i wouldn't know where to start and its this that, that i needed when i said plain English in my original post. I don't understand (yet) what
Dim r As DAO.Recordset Dim txSearchBox As String Dim sSQL As String means or the rest of it - i've been learning slowly and am no where near that standard.

Well, You have used Macros (aka Scripting) which I never use - it is even hard for me to read because I have never spent any time trying to learn it. Macros are too limiting.
VBa is how most of the complicated stuff is accomplished. I have 1 routine that is 56 letter sized pages long. Can't do that with a macro.


So it looks like you have 25 different "rooms" with stations in them. I kinda understand why so many buttons. But since I only know what you have said, it is very difficult to give a helpful answer.

For instance, you said

I have Main form called "Blank Form" - it has a custom links menu.
The idea is to copy all its contents to every form before i start populating the other forms.

Are the forms open? Cannot add data to a closed form.
Is the form bound or unbound? If bound, are the controls on the form bound or unbound?

One method is to have (hidden) unbound controls (text boxes) on the main form ("Blank form"), and have code to push the data to the form that opened.
Or you can have the form that opens grab the data from the main form (hidden) unbound controls.

I just don't know enough about your project...

You really should go through your objects and remove any spaces between words. If you MUST separate the words, use the underscore.
Examples:
table name: "1-Main Information Sheet" would be "MainInformationSheet1" or "MainInformationSheet_1"
field name: "Office Installed" would be "OfficeInstalled" or "Office_Installed"
Query: "Desk Searcher Query" would be "DeskSearcherQuery" or "Desk_Searcher_Query"

BTW, this, "CamelBackFormat" is called camel back format.


Did you draw out the table designs on paper? Did you try walking through the design using data to see if the design works?
Did you draw out the forms on paper to see how they interact?

Do you have a better design (not so many missing objects) available that you could/would post? Only needs maybe 5 records.........



Subject of VBA programming:
All programming languages have common concepts: program control (ie Looping), defining variables, branching, calculations, ...
It is a matter of learning the syntax of the language you want to program in.

You might start out with https://www.youtube.com/watch?v=kogGwRIHH6o

Another site to look at is: http://www.functionx.com/vbaccess/ On the left, there is a list of lessons. Start with the first and work through any examples. Don't just read - actually do the lesson in Access. That is the fastest way to learn (at least for me).

[Oculus82]

I will tidy everything up on it and send it you on a PM as you wrote the Quick search Code and i've started populating it with info (i do appreciate your time)

Everything is working so far but that quick search needs a little tweak so it copy's the searched details to every newly opened form .. (its ok when you add data to the quick search on form 1 but when you goto form 2 you have to enter the details again - i'd like it to copy what was just entered.)

Will get back you you later today after is ripped it to bits and "camelbacked" it under your advisement

[ssanfu]

I've highly modified your dB. (renamed tables, combined forms, renamed controls, added code, ...)
With the controls having the DLookup() function for the control source, you cannot add "a little tweak so it copy's the searched details to every newly opened form".
You did have the record source for the forms to a table, so I combined the search forms into one form using the tab control.

If you don't like this I will send you your old dB with some code to try and copy the searched details to every newly opened form.

[Oculus82]

Hi Steve.

Ive had a look at the modified DB but i need those search form accessible on all forms rather than on 1 form for ease

However.. i see you attached the quick search to all the other search forms (propagated the information from the quick search) - this isn't needed.

That quick search is independent from the search forms - its only purpose is for finding location information based on assets searched.
I though that using a sub form for the quick search, the information entered would automatically be pulled onto any new forms opened but it doesn't, hence why i thought your code just needed a tweak lol.

I've attached 2x images of what i mean for it to do.
Attachment 33349
Attachment 33350

At the moment your code just pulls the searched info on 1 form but when you click another the information gets cleared.

Again.. i really do appreciate your assistance and cant thank you enough if you weren't half way around the world i'd buy you a beer or 3

I've highly modified your dB. (renamed tables, combined forms, renamed controls, added code, ...)
With the controls having the DLookup() function for the control source, you cannot add "a little tweak so it copy's the searched details to every newly opened form".
You did have the record source for the forms to a table, so I combined the search forms into one form using the tab control.

If you don't like this I will send you your old dB with some code to try and copy the searched details to every newly opened form.

[ssanfu]

Ive had a look at the modified DB but i need those search form accessible on all forms rather than on 1 form for ease

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".

However.. i see you attached the quick search to all the other search forms (propagated the information from the quick search) - this isn't needed.

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:

I though that using a sub form for the quick search, the information entered would automatically be pulled onto any new forms opened but it doesn't, hence why i thought your code just needed a tweak lol.

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

At the moment your code just pulls the searched info on 1 form but when you click another the information gets cleared.

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....