473,322 Members | 1,408 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,322 software developers and data experts.

Derive Field Based on 2 other fields

21
I have a form where I want a third field to be autofilled by a certain text based on the content of 2 previously entered fields of data.
I put this following code into an on_click event of a button. (I dont care much on how it happens, I only want it to fill)

Expand|Select|Wrap|Line Numbers
  1.     If (Me.DepCity = HAM & Me.DestCity = PEK) Then
  2.     Me.DepHandlingAgent = "DSVHAM"
  3.     End If 
  4.  
But the problem is that no matter what data is in the first 2 fields the 3rd field gets the value thats in the if condition.

What can I do?

edit:
When I use double quotes for the "HAM" and "PEK" its not filled at all.
Apr 12 '11 #1

✓ answered by colintis

Try the following
Expand|Select|Wrap|Line Numbers
  1. If Me.DepCity = "HAM" AND Me.DestCity = "PEK" Then
  2.     Me.DepHandlingAgent = "DSVHAM"
  3. End If 

3 2062
colintis
255 100+
Try the following
Expand|Select|Wrap|Line Numbers
  1. If Me.DepCity = "HAM" AND Me.DestCity = "PEK" Then
  2.     Me.DepHandlingAgent = "DSVHAM"
  3. End If 
Apr 12 '11 #2
dileshw
21
Worked like a charm!!!
Thanks Colintis!
Apr 12 '11 #3
TheSmileyCoder
2,322 Expert Mod 2GB
Good one, colintis.

I just want to add some additional information:
When you write Me.DepCity = HAM you are comparing the VALUE of Me.DepCity to the value of the variable HAM (And since you have not declared a variable HAM, access will create an empty one for you, of type variant). Still the comparison gives no meaning. To avoid such errors in the future, ensure that you always have the line OPTION EXPLICIT at the top of your code module.

Now, the & is a string concatation operator. It will basicly take the result of your first comparison and your second comparision and parse it to a string. For example:
Expand|Select|Wrap|Line Numbers
  1. Me.DepCity = "HAM" & Me.DestCity = "PEK"
would yield:
Expand|Select|Wrap|Line Numbers
  1. TRUE & True
which would concatanete into:
Expand|Select|Wrap|Line Numbers
  1. "TrueTrue"
a string with the value "TrueTrue"

I hope that I managed to clear up some misunderstandings and not just add to the confusion.
Apr 12 '11 #4

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

Similar topics

1
by: Don Leverton | last post by:
Hi Folks, I have been given a CD with approx 130 .xls files (bean-counters!) that I would like to import and merge to ONE table (tblTradeshow). The XL files are *similarly*, but not...
2
by: David | last post by:
Hi, This is probably really simple but kinda has me stumped. I have taken over a data base and am looking to modify it to make it more user friendly. Currently, I have a Combo box with 4...
14
by: SpyderSL | last post by:
Hey, I have created an access form, in which I have a drop down with employee names. These are the steps I would like to happen: 1. A user will enter a number in FIELD A 2. The user will...
1
by: Neha Jain | last post by:
i have a table 'Tab1' with some fields. i want to create a screen consisting one of the fields in Table 'Tab1'.user must be able to select any of the data stored in the table for that field and any...
1
by: compl | last post by:
I have a database that I need to restrict the ability to edit one field based on another. The two fields are DOS (date) and Amount (currency). I only want the users to be able to edit the Amount...
4
by: whamo | last post by:
I have the need to populate a field based on the selection in a combo box. Starting out simple. (2) tables tbl_OSE_Info and tbl_Input; tbl_OSE_Info has three fields: Key, OSE_Name and OSE_Wt...
5
by: Shortstuff12345 | last post by:
I'm trying to use VBA code to disable a field based on the value of another field in a form. The code I have properly updates the enabled property of the field when it changes; however, it changes...
3
by: BobbyD1120 | last post by:
I have created a inventory tracking database and I want to show/hide certain fields based on the device category. The deviceCategory field is a lookup field to a table that lists all the different...
1
by: Emily Walshaw | last post by:
I'm sorry, I know this has been asked a million times, but am completely new to access and need idiot proof help. I have a database and I am struggling to get a field to update based on a combobox...
8
sueb
by: sueb | last post by:
I inherited a database that contains three fields: - OR Time (a number) - OR Time In Room (Date/Time) - OR Time Out Room (Date/Time) On the form is an unbounded field that correctly...
1
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: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.