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.
8 6788
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 32,573
Recognized Expert Moderator MVP
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.
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 - Move the DLookUp() function to the event named Col1Field_After Update() (replace Col1Field with your actual textbox name) and
- 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 32,573
Recognized Expert Moderator MVP
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
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";"50 3"...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 32,573
Recognized Expert Moderator MVP
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?
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 32,573
Recognized Expert Moderator MVP
The event is the AfterUpdate event. Does that make sense to you? If not we can go into more detail.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Eugene |
last post by:
Hello All,
I need a report in Access 2007 to show a dynamic image on every page.
I have a table, and I store image's path in a cell in each of the
rows.
In 2003, I was able to do that using VB, the following code worked:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me!.Picture = Me!
|
by: TimmyNZER |
last post by:
Hi,
I would like to be able to have a button on a form, that when clicked will add specific information from that form to a table.
I would be greatful to anybody who can tell me how to do this! I assume that I have to give the 'text boxes' a specific name, but I need the code for the button that will search for that specific text box, then add it to a specified table.
Many thanks in advance,
Tim Matthews
|
by: patio |
last post by:
MS Access 2007 Form. I need to create either check boxes or a mutivalued list in a form where the user can select more than one item. For example, I am creating a call list that tracks various information including the type of call or 'Topic Code'. The user wants to be able to track what the topic is for each call - but there may be several topics in one call, therefore the need to pick one or several options from the topic code lookup table for...
|
by: ARC |
last post by:
This is taken from the "What's New in Access 2007" page. However, I've
looked through all the properties of a text field memo box, and cannot find
the append only option. Does anyone know how to use this feature?
------------------
"Memo fields are useful for storing large amounts of information. With
Office Access 2007, you can set the Append Only property to retain a history
of all changes to a Memo field. You can then view a history...
|
by: rickcross |
last post by:
I am trying to use the Access 2007 runtime. I have a program that is
fully working in 2007 but when I install the runtime version with same
Operating system and Access 2003 installed I have multiple errors.
One error "Function is not available in table level validation
expression."
This error occurs three times. when I have a two part delete query or
a lookup function that checks for a duplicate entry and a update
query that adds a...
| |
by: nico5038 |
last post by:
Access 2007 Linkedtable manager refuses to relink tables having a field with the "Attachment" datatype.
Problem:
When placing a split database in another folder, the Linked table manager should be used to relink the tables. The Linkedtable manager does however refuse to relink tables having an "Attachment" datatype field.
Symptoms:
Open the Linkedtable manager and select a linked table with an "Attachment" datatype field. Now try to...
|
by: haniss |
last post by:
After importing a txt delimited file into Access 2007 with adding Couonter
there is a problem with column order. For example in txt file:
John|Doe|25.10.2007
in new Access table are 4 columns:
Doe|John|1|25.10.2007
I need the same column order!
|
by: Jim Mandala |
last post by:
I had a user that converted an application from Access 2003 to Access
2007. The application is split front-end/backj end. When trying to
open certain forms, the front end reportst that they don't have read
permissions on the table. Neverthess, it is possible to actually open
(and read the table directly while still in the front end.
We fixed the References (I think) and ran compact and repair on both
the front end and back end.
Any...
|
by: netnewbie78 |
last post by:
Hello All,
I don't have a problem (but maybe I will after I explain). I have a
question with regards to something I saw in Access 2007. But first, a
little backstory:
I'm writing a very small stock database. For now, it will simply track
what products come in (Stocks bought by Project) and what products go
out (Stocks sold to by Project) . There are three tables: Products,
Transactions and Projects.
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |