Table Column source from LookUp function (Access 2007)

[mguy27]

We have about 2 dozen security officers who patrol about 120 buildings. They find defective or unlocked doors and write a "Trouble Report" on the door. This card gets turned in, where I have designed an Access Field and Table to enter the information and store it. They then get emailed to their respective supervisors so the problems can be dealt with.

As of right now, we have a big list on the wall of the buildings and their supervisors. After entering the building abbreviation, we have to look on the wall for who it goes to, and enter that name into a text box. Then, once we have entered all the TR's for the night, we open up the table and mark the ones to be sent out (using a print query and checkbox). Next to that check box is the name of the person we entered, so we can see who to send it to. This probably isn't making any sense, so I'll try to cut to the chase:

I can easily set up a DLookUp function as a Text Box in the Form. This displays who the TR should get sent to. But I can't get it to record in the table. So, while I can see it in the form, that is no use to me. I need a DLookUp type function as a column in a Table.

So here's my question: how do I set a function to a table column? The idea is this:
If Row 1 Column 1 reads "A", I want Row 1, Column 2 to read "B"
If Row 2 Column 2 reads "C", I want Row 2, Column 2 to read "D"

Col 1 Col 2
A..........B
C..........D
E..........F
E..........F
C..........D
A..........B
E..........F
A..........B
C..........D
C..........D
E..........F

Where Col 1 is entered manually, and Col 2 is automatically entered using some kind of LookUp function.

I hope this hasn't confused anyone too much. Let me know if it needs more clarification.

[PianoMan64]

We have about 2 dozen security officers who patrol about 120 buildings. They find defective or unlocked doors and write a "Trouble Report" on the door. This card gets turned in, where I have designed an Access Field and Table to enter the information and store it. They then get emailed to their respective supervisors so the problems can be dealt with.

As of right now, we have a big list on the wall of the buildings and their supervisors. After entering the building abbreviation, we have to look on the wall for who it goes to, and enter that name into a text box. Then, once we have entered all the TR's for the night, we open up the table and mark the ones to be sent out (using a print query and checkbox). Next to that check box is the name of the person we entered, so we can see who to send it to. This probably isn't making any sense, so I'll try to cut to the chase:

I can easily set up a DLookUp function as a Text Box in the Form. This displays who the TR should get sent to. But I can't get it to record in the table. So, while I can see it in the form, that is no use to me. I need a DLookUp type function as a column in a Table.

So here's my question: how do I set a function to a table column? The idea is this:
If Row 1 Column 1 reads "A", I want Row 1, Column 2 to read "B"
If Row 2 Column 2 reads "C", I want Row 2, Column 2 to read "D"

Col 1 Col 2
A..........B
C..........D
E..........F
E..........F
C..........D
A..........B
E..........F
A..........B
C..........D
C..........D
E..........F

Where Col 1 is entered manually, and Col 2 is automatically entered using some kind of LookUp function.

I hope this hasn't confused anyone too much. Let me know if it needs more clarification.

Instead of using the table to create what it is that you want, you simply create a query that will lookup the information for you and display that result in the report or whatever you're wanting to display.

Create a query with both the tables and link them by col1 with the cross reference list.

Hope that helps,

Joe P.

[NeoPa]

It seems to me that you're simply selecting the next letter of the alphabet. Is this the case or is that an accident of your example?

Also, are you doing this in Access or Excel? your text refers to DLookUp() which is an Excel function, yet you also refer to tables.

[missinglinq]

First off,

...are you doing this in Access or Excel? your text refers to DLookUp() which is an Excel function, yet you also refer to tables.

Huh? HLookup() and even plain old LookUp() are Excel functions, but not DlookUp()!

Next,

I can easily set up a DLookUp function as a Text Box in the Form. This displays who the TR should get sent to. But I can't get it to record in the table. So, while I can see it in the form, that is no use to me. I need a DLookUp type function as a column in a Table.

When the content of a textbox isn't saved to a table, it's because the textbox is not bound to a field in the underlying table/query. In Design View for your form, if you select the Col2 textbox and go to Properties - Data what appears in the ControlSource property box?

If it's empty, use the down arrow and select the field in your table that you want to use to save the value for Col2.

If it's not empty, what's in there? Is it the DLookUp() function that you've referred to for finding the value of Col2? If this is so, you need to

1. Move the DLookUp() function to the event named Col1Field_AfterUpdate() (replace Col1Field with your actual textbox name) and
2. as outlined above, use the down arrow of the ControlSource property and select the field in your table that you want to use to save the value for Col2.

And finally, as to NeoPa's second question

It seems to me that you're simply selecting the next letter of the alphabet. Is this the case or is that an accident of your example?

If this is true, that you simply need to choose the next letter in line to populate Col2, you wouldn't need to use DLookUp() at all, but simply

Private Sub Col1Field_AfterUpdate()
   If Me.Col1Field <> "Z" Then
    Me.Col2Field = Chr(Asc(Me.Col1Field) + 1)
   Else
   'Do here whatever you want to do if Me.Col1Field = "Z"
   End If
  End Sub

Welcome to bytes!

Linq ;0)>

[NeoPa]

First off,

Huh? HLookup() and even plain old LookUp() are Excel functions, but not DlookUp()!
...

You're absolutely right of course. I was getting confused :S
I was thinking of VLookup() :D

[mguy27]

I've been away from the office for about 2 weeks, and now that I'm back, I'm working a little bit on this ongoing problem.

Orignally I had tried the query thing, and couldn't get it to work. I've gone back to trying to work with the query, and here's my dilema(s):

As it stands, there are 2 fields in a form. The first (named simply: #) is the security guard's number. The second field is their last name (named: Officer). What I want to have happen is this: I type in the security officer's number (they range from 501 to 524) and up pops his last name in the next field. The two fields right now are just combo boxes and the row source is like this: "501";"502";"503"...etc.

So, I've made a query, but it seems to be too simple/1-sided. I have a table. In column 1 (labled OfcrNum) I have the numbers (501, 502, 503, etc.). In column 2 (labled OfcrNam), I have the corresponding last names (Johnson, Watts, Francis, etc.) When I build the simple query and select OfcrNam, all it returns is the list of their names in the order they are found in the table. So I put that query is as the record source for my combo box back in my form in the Row Source box under properties, and it gives me that list...but that's not what I want. I want some kind of "auto fill" that will put the corresponding name into the field Officer when his number is typed into the field #.

I hope that isn't too confusing. And earlier, I wasn't using actual letters. I was just using A, B, C, D, etc. as an example representation for corresponding officer numbers and names.


Later, I want to be able to do the same thing with building names and supervisors' names. Like I said earlier, these trouble reports are written for specific buildings, and need to go to their respective supervisors. I'd love to be able to type in the building name into textbox "Building" and have it auto fill with the corresponding supervisor into textbox "Super".

Any hints to such a simple problem would be great. Thanks so much for all your help!!

-Taylor

[NeoPa]

If you create a ComboBox on your form with a query as the source returning the # & name from your table, you can hide the name column from your ComboBox then set the value of your name TextBox field from the results of your ComboBox in its AfterUpdate event procedure.

Does that sound like it may solve your problem?

[mguy27]

What kind of event would it be? like a DLookup? or another query?

I played around with an update query to try and put that in, but couldn't get it to iron out.I'm frustrated because this seems so simple, yet for whatever reason I am missing the "key" to bring it all together.

[NeoPa]

The event is the AfterUpdate event. Does that make sense to you? If not we can go into more detail.