Unsure of DLookUp

[ClearlyAnIdiot]

I have a piece of code, and when it's run, the system doesn't detect any syntax errors.
However, as far as I can see, it does absolutely nothing. I had a tonne of syntax errors before, but after correcting them all, it does nothing.
There are two tables. One is that of the companies, which has its related form open, and the other is that of a child table of a company and its related people. I want it so that the dlookup will look for all the "name" and "rolesperformed" records of things in the 2nd table where the company number is the same as the one in the form, which is my primary key.

Private Sub Lookup_Click()
Dim Derpy As Variant, Derpie As Variant
Derpy = DLookup("[Name]", "Company-personID", _
"CompanyNo = " & [Forms]![Company info]![CompanyNo])
Derpie = DLookup("[RolesPerformed]", "Company-personID", "CompanyNo = " & [Forms]![Company info]![CompanyNo])
CombinedFields = [Derpy] & [Derpie]
End Sub

How can I get it to display both of those upon clicking? It doesn't really matter to me how they're displayed, as long as they are. Presently, when I click on the command button "lookup", it doesn't do anything.

 

[pr2-eugin]

A few things that needs to be noted here.. You mention..

I want it so that the dlookup will look for all the "name" and "rolesperformed" records of things

I hate to be the bearer of bad news, but DLookUp only returns one Result.. So if the same company had 10 names then only the first one is given out.. If you want to get all.. You might want to look into Working with Recordsets..

Also, my guess is that the CompanyNo is in the Form where the button resides, so use Me... Always use the right Type.. Wrap DLookUp with Nz to see the difference between "No Value" and "No Result"..

Try the modified code..

Private Sub Lookup_Click()
    Dim Derpy As String, Derpie As String
    Derpy = Nz(DLookup("[Name]", "Company-personID", "CompanyNo = " & Me.CompanyNo), "NA")
    Derpie = Nz(DLookup("[RolesPerformed]", "Company-personID", "CompanyNo = " & Me.CompanyNo), "NA")
    Me.CombinedFields = [Derpy] & " - " & [Derpie]
End Sub

 

[ClearlyAnIdiot]

I'm not sure of why, but my access doesn't recognise "combinedfields" as a method, suddenly. On top of that, I tried just "derpy" first by itself, and nothing happened when I clicked the command button, just as before.
I just noticed "combinedfields" on another forum post around here. Could it be that "combinedfields" was a custom module that I have to programme in first

 

[DavidAtWork]

yes, from your original post I would say CombinedFields was a function or sub from where ever you lifted the code. You could modify your form and add a new unbound textbox, name it CombinedFields and Pr2's code should work

David

 

[CJ_London]

I think you will find that Combinedfields is the name of a text control you need to create on your form which will display [Derpy] & " - " & [Derpie]

 

[ClearlyAnIdiot]

Heh, I couldn't find the original post where "combinedfields" was used. Is there an alternative that you could recommend to open both fields, and all related records? I don't mind whether it's opened with recordset or whatever in the end, as long as I can view it. It's a relatively small database of around 80 companies, so I don't mind slow processing.

 

[pr2-eugin]

Okay, to provide an apt and correct answer, we need some sample Data, and a result set that you wish to see..

BTW, did you try the code I provided in Post#2?

 

[CJ_London]

probably the easiest way would be to use a subform.

It looks like this is simply a continous or datasheet form based on your 'child' table

In the subform, set the linkchild and linkmaster properties to CompanyNo - this assumes these are the PK and FK names of your fields

 

[ClearlyAnIdiot]

Yes, pr2, I tried the code, but I took Combinedfields mindlessly from some obscure forum post somewhere ages ago, and assumed it would be a standard method, so it didn't work. Also, the individual codes didn't work at all, it would appear. Nothing was happening.
CJ, I'll try to search up what a subform is, first. I'm that new to Access .

 

[pr2-eugin]

I second CJ's suggestion of using SubForm.. That is the dead plain answer to your trouble..

 

[ClearlyAnIdiot]

Ehehhehehehehehehehehhehehehhe, it turns out that the subform solved pretty much the entire thing I was working on for about a week.