populate an label from an table

[white_flag]

Hello

I like to know how can I populate an label.caption from an table

I have this: but it is populated just the first entry from table:

Private Sub Form_Load() 
    Me.Controls("Label1").Caption = DLookup("[company name]", "companies") 
End Sub

[boblarson]

Just change the label to a text box (and format it to look like a label) and then set the control source to

=DLookup("[company name]", "companies")

I take it that the company name is the same for all records? If not, then you are missing criteria on the DLookup.

[white_flag]

thx Bob,

but on the text box, I have just the first entry (the same like in an label case) .. play with DLookup () but nothing.

another ideea?

[boblarson]

thx Bob,

but on the text box, I have just the first entry (the same like in an label case) .. play with DLookup () but nothing.

another ideea?

The problem is that your DLookup as it is will ONLY ever return the first record from the query. That is what I meant about CRITERIA needing to be applied. So, what field should it be looking at to determine which record to return? What field in the current record will determine which company name to bring back?

[white_flag]

an DLookup can show all the records from an column (table: companies, columns: company_names; location ...etc)

all the records from column company_names

[rpeare]

What bob's saying is that unless your table COMPANIES only has one row, your dlookup is always going to just look at the very first record it finds. You have to supply the dlookup with criteria if you're looking for a specific record for instance instead of:

[code]dlookup("[Company Name]", "Companies")[code]

You would have

dlookup("[Company Name]", "Companies", "[CompanyID] = 1")

The second one will actually look for the company name for the company where the primary key is 1

[boblarson]

Yep, as mentioned, a DLookup ONLY returns ONE record. And if you don't give it some criteria it will return the first record that the table or query that you have specified would return if it was opened. If you want to have your DLookup return the company for the row that the data is on, personally I would just include that table in the query and then select the field for it. It would be much more efficient and no delay in displaying it.

But if you insist on using the DLookup it could use this:

=DLookup("[Company Name]", "Companies", "[CompanyID] = " & [CompanyID])

So if you have a field named CompanyID in your companies table (which you should) then you can lookup the company name based on the companyID that is in the control or field named CompanyID on the form (which is why I have it concatenated into the value. If CompanyID is text then it would need to be:

=DLookup("[Company Name]", "Companies", "[CompanyID] = " & Chr(34) & [CompanyID] & Chr(34))

[white_flag]

thx guys that you are spending your time with this one. Ok I put in attachment "the data base"

[rpeare]

the syntax you want for the dlookup is:

Me.Controls("Label1").Caption = DLookup("[FirstName]", "Employees", "[EmployeeID] = " & [EmployeeID] & "")

but you will have to put the employeeID on the form (you can make the VISIBLE property FALSE and still have it function.

I don't know why you want a label with the contents of something that's already on your form but if you don't want people messing with the data (which I suspect is the case) you can set the LOCKED property of the TEXT BOX containing their first name to YES.

Which is by far an easier solution because you will have to refresh the dlookup every time you change records. What if you choose a hire date where you have 7 employees? and you want to browse through them? You're going to have to update this field every time you move to another employee and as the database grows this will become a more noticable impedement.

[white_flag]

ok, What I like to do (but I do not have the skills): I have some records that are having the same date. I like to be displayed all records from that date not just the first one. The result will be puted in an textbox locked). See attachment

[rpeare]

I don't think I need to look at the database again. Highlight the field(s) you DO NOT want to give the user to modify, open the properties window, go to the LOCKED property and set it to YES.

When you click your calendar it will bring up everyone with a hire date associated with it but the user will not be able to modify any of the fields that are LOCKED. What I do is also change the BACKGROUND Color to something noticable shade other than white so it's clear which fields are locked. TO aid you you can also change the TAB STOP proper to NO which means when your users hit enter it will ignore the field where this property is set.

[white_flag]

I did that (locked the text box). now the result to be displayed in textbox (records that are having the same date).the second attachmen is diffrent then the first one .

[rpeare]

I don't know what you mean by the second attachment is different. the way you have your form set up each person is going to appear on a different form. If you're looking for a way to lost all people that were hired on a the same date that's an entirely different problem than the one you've asked.

I created a form called frmEmployees (yours was misspelled frmEpmloyees) that will show all employees for the date you click is this what you're really after?

[white_flag]

thx mate. It is looking nice. better with an listbox. one more thing (if it is possible) If I will like to clik on one name from listbox, it is possible to open an user form with the date from that persson?

[rpeare]

yes, just use the ON CLICK event of the list box to open whatever form you want. Just make sure that the RECORD SOURCE of the form you open is based on a query with the criteria of being forms!formname!listboxname