473,288 Members | 1,710 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

Automatic Update of Field on a Form

Hello,

I am having difficulty programming a command in a form that i have created in my database. Basically, I have a form where there are four areas that the user is instructed to input information: Account, Group, User Name and Date. All four sections are set up as dropdown boxes. What I am trying to do is create a command that automatically updates the "Group" Name based on the "Account" number that is entered on the form by the user.

This is the command that i have input in the control source section of the text box created for 'Group' on my form:

=DLookUp("[Group]","Accounts","Oracle Account Number"="[Account Number]")

What I thought this would accomplish is to look for the field 'Group' on my form, by searching in the 'Accounts' table (that is created and is my driver of all accont-group combinations) and returning a 'Group' name where the 'Oracle Account Number' in the 'Accounts' table matches the 'Account Number' listed on my form.

Can anyone provide some assistance with this? As an additional piece of information, and I am not sure if it matters, the entire form has a record source of an entirely different table.
Jun 21 '07 #1
12 7107
MMcCarthy
14,534 Expert Mod 8TB
I can't really follow the logic of what you are asking. However, I think you should check out the following tutorials as they may be able to help you.

Cascading combo/list boxes
Filtering on a form
Jun 22 '07 #2
Thanks.

Let me try to clarify what I am trying to do.

I have four fields on a form that are all combo boxes, and the users are instructed to make selections for all three. I want to change one of those boxes to a tex box, one that automatically updates once one of the other combo boxes is populated.

To illustrate, the one combo box is named 'OracleAccountNumber'. Once it has an account number, I want the text box, 'Group', to automatically update with its assigned group.

I have a table named 'Accounts' where all Account-Group combinations are stored. Maybe the code I was presenting was the wrong way to go about it.

Does that clear it up? At the end of the day, I don't want the group to be something that is chosen by the user, but I still want the user to be able to see the Group listed on this form.

Thanks for the response.
Jun 22 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
Do the tutorials I gave you not solve these problems. The cascading combo boxes at least should solve the first part.
Jun 22 '07 #4
well, i have read your tutorials, and i have tried to mimic what it was telling me and create the database being taught, and i cant even get that to work.

Below is the code that i input:

Private Sub Company_AfterUpdate()
With Me![Employee Name]
If IsNull(Me!Company) Then
.RowSource = ""
Else
.RowSource = "SELECT [EmpName] " & _
"FROM TblEmployees " & _
"WHERE [ID]=" & Me!Company
End If
Call .Requery
End With
End Sub

And it keeps getting hung up on this section of the above code.

.RowSource = "SELECT [EmpName] " & _
"FROM TblEmployees " & _
"WHERE [ID]=" & Me!Company

I think my problem goes back to this part of the tutorial:

As a default, [Company] will have the following properties:
Row Source – TblCompany
Column Count – 2 (We use 2 columns because we want to include both ID and CpyName.)
Column Widths – 0”;1” (We set the first column to 0” because the user does not need to see the ID.)
Bound Column – 1 (We bind it to the first column so that when we refer to [Company], it will return the ID rather than CpyName.)

What is this telling me to do and how?
Jun 26 '07 #5
MMcCarthy
14,534 Expert Mod 8TB
Forget the If is null code. You want it to change everytime someone changes the selection in Company.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Company_AfterUpdate()
  2.     With Me![Employee Name]
  3.         .RowSource = ""
  4.         .RowSource = "SELECT [EmpName] " & _
  5.                             "FROM TblEmployees " & _
  6.                             "WHERE [ID]=" & Me!Company
  7.         .Requery
  8.     End With
  9. End Sub
  10.  
Now your query states
"WHERE [ID]=" & Me!Company

This indicates that there is a field in TblEmployees called [ID] which is a foreign key reference to the [ID] field in TblComapany. Also I assume the [ID] field is a number type field.

So assuming you have a table as follows:

TblCompany
ID (Primary key, Autonumber)
CompanyName

Then the row source of the combobox will be TblCompany. Set the following properties for the combobox

Under the Format Tab
Column Count = 2
Column Widths = "0cm;3cm"

Under the Data Tab
Bound Column = 1

This will set the combobox to display only the company name but store the [ID] field for use later.
Jun 26 '07 #6
Thanks for your assistance with this. Sorry for the delay; I got tied up in some other tasks.

Ok. I seem to have gotten it to work correctly; well, at least it appears that way. I have two combo boxes in my form, and when i select the company in its combo box, i can then only make the selections from the employee list that match the id. Success there

But, I have one last thing that is not working. When I select any of my fictional companies, and I attempt to choose an employee from the drop down box that pertains to this company, I keep getting an error message that the "Text value you entered isn't valid for this field"

I am not sure why I am getting this message.

Thanks again for your assistance with this.
Jul 24 '07 #7
Never mind...I think i got it.
Jul 24 '07 #8
Thanks again. I believe that it is working now.

I do have another request, and I apologize if this is somewhere else in our discussion.

Lets say, for example, that I wanted to still have my combo box as a listing of all of the companies. But, let's assume that each company only has one person employed. Then, this would eliminate the need for another drop down box to list the employees. My guess is that I would need to change my code in the 'Employee' 'text box' , but what would that change be?

For example, lets say I choose ABC enterprises, and ABC only has one person, John Doe, working for them. Thus, I would want John Doe to appear in the employee box automatically when i choose ABC enterprises.

How can I do that?
Jul 24 '07 #9
MMcCarthy
14,534 Expert Mod 8TB
Try something like this...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Company_AfterUpdate()
  2.  
  3.     Me.txtEmployee = DLookup("[EmpName]", "TblEmployees", "[ID]=" & Me!Company)
  4.  
  5. End Sub
  6.  
Jul 24 '07 #10
I'm not sure if I understand your question properly, but it seems to me that your employee box is a combo box. In that case, i think you could keep it as a combo box and just set the default value to "[Employee Name].ItemData(0)" and the first (and only) value will show up in your combo box.
Jul 24 '07 #11
I tried the vba code provided, and it seems to be getting hung up on this:

.txtEmployeeName =

Any ideas? Maybe a text box isnt right for this. In the end, I just want the information in the Employee box to be 'driven' by the selection in the company combo box. Whats the best way to do this?

As for the default code suggested, it worked...but it didnt change when i made my selection in the company combo box. Basically, it showed the first value from the bound list, but when i selected a company, it still showed the first employee on the list and not the first employee that relates to the selected company.
Jul 25 '07 #12
MMcCarthy
14,534 Expert Mod 8TB
I tried the vba code provided, and it seems to be getting hung up on this:

.txtEmployeeName =
I used

Me.txtEmployeeName

as an example of the name for your textbox. Check the actual name of your textbox and replace this.
Jul 25 '07 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Rene Crespo | last post by:
Hello, Sorry about the length of this explanation! I am using Access 2000 to assist an export sales department keep track of its weekly offers of products to clients. The database already...
4
by: Vincent Yang | last post by:
I'm using Access 2002. My database is in Access 2000 format. I'm designing a form to collect responses to a 40-item questionnaire. I can fit four list boxes on a screen, so I plan to have 11 tab...
0
by: Dave | last post by:
Hi, I'm creating a form based on a table. This table has fields including UniqueID (auto numbered and different for each entry), Track (3 different types), ClassYear (4 different years), and...
1
by: Kelly Olsen | last post by:
I have created an automatic update field in my Vendor_Table that automatically updates the date in that field to respond to any changes made to the record. However, I would like to display this...
4
by: Bob Alston | last post by:
I have a subform with multiple records tied to a main record and main form. In the subform I create and update the subform records. However, there are other pieces of data, statistics, I want to...
1
by: Earl Anderson | last post by:
Using WinXP & AccessXP, I'd like to automatically have the records that I've just printed in a report automatically updated to have the 'print date & time' immediately inserted into a 'Date/Time'...
1
by: rdemyan via AccessMonster.com | last post by:
My App has 10 or so tables that we provide that contains proprietary data. This data will need to be updated once or twice a year. I would like some comments, suggestions on my proposed strategy...
7
by: CodeGunnerLev1 | last post by:
Good day guys... I must say this forum is quite of useful one. I really appreciate what you guys are doing and I hope it will continue on. I have few questions that needs some help. 1. I want...
1
by: sconard | last post by:
When you create a form based on a table via access 2007 wizard, form will update when moving from field that has changed to another field within form. Each loss of focus in "changed" fields causes...
9
by: Brett_A | last post by:
I have a form where the first field is a dynamic drop-down that pulls from a db (Access). The fields associated with the query are task_id, task_name and task_rate. The field has the value of...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.