473,327 Members | 2,118 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,327 software developers and data experts.

IsNull

37
Hi I am using Access 2007. I have a form created from two tables. On each table I have a field named haul which needs to be filled out for every record and the value is same for corresponding record on the other table. The tables are linked by the ID field. Because I'm not very experienced with Access I made the form so that the user has to enter the value twice which is a pain. Is there a function I can use to tell the program if the value for one haul record for one table is null take the value from the other table haul field with the same ID.

example: table one named : Commerical Important Haul log, field name HAUL.
table two: Discarded Species Haul log, field name HAUL

Form:
Haul Number, this value goes to table 1, Discard Haul Number this value goes to table 2.
I would like to get rid of the Discard Haul Number on the form. Thanks for any help.
Oct 11 '08 #1
8 1503
Delerna
1,134 Expert 1GB
Here is one way
Set the enabled property for the "Discard Haul Number" textbox to "No".

Then in On KeyUp event for the "Haul Number" textbox enter this code

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtHaulNumber_KeyUp(KeyCode As Integer, Shift As Integer)
  2.    txtDiscardHaulNumber = txtHaulNumber.Text
  3.  
  4.    'COMMENTS
  5.    'Notice txtDiscardHaulNumber has no .Text on the end
  6.    'If I did that then you would get the error
  7.    'txtDiscardHaulNumber must have focus
  8. End Sub
  9.  
I have given the text boxes names of my choosing. You need to either
1) change the names in my code to match the names for your textboxes
or
2) change the names for your textboxes to match the names in my code


PS
I do hope you are giving the controlls on your forms meaningful names
I have used the hungarian notation "txt" so that in the code I will be able to come back in the future and see immediately that the code is updating the value in a textbox to match the value in another textbox.
Some other prefixes are tbl,qry,frm,rpt,txt,cbo,lst,opt,lbl
Oct 12 '08 #2
sillyr
37
I changed your text to match my text boxes.
For Haul number the text box is Text486 and for Discard Haul the test box is Text490.

I copied your code and changed the names, but am getting an error message- you have entered an operand without an operator.


= Private Sub Text486_KeyUp(KeyCode As Integer, Shift As Integer)
Text490 = Text486.Text
Oct 13 '08 #3
Delerna
1,134 Expert 1GB
why is there an = sign before Private?


operator=operand
no error



=Private
error no operator
Oct 13 '08 #4
sillyr
37
Sorry- Access adds in the = sign after I typed in the code. I deleted the =, but am now getting an error message when the form is in form view. I enter the Haul number in text box 486 and get the error:
can't find the object "Private Sub [Text486] _KeyUp(KeyCode As Integer, Shift As Integer)
[Text490] = [Text486] .Text
Oct 13 '08 #5
Delerna
1,134 Expert 1GB
Ok, there is a difference between access 2007 (your version) and access 2003 (my version) and the way the keyup event is called.

remove all of the code
Goto the properties for text486 from the form design view
find the "key up" event in the properties dialog and add code

You should finish up back on the code page with the txt486_keyup subroutine
something like
Private Sub txt486_KeyUp(KeyCode As Integer, Shift As Integer)

End Sub
but the parameters will be different

Now just add the line
Text490 = Text486.Text
into that subroutine.
Oct 14 '08 #6
Megalog
378 Expert 256MB
Sounds like you're putting code in through the Expression Builder.

When you go to the event, under the object's properties, (like described in the post above this) click the box with "...".

This will bring up the Choose Builder prompt.. Here you want to go to "Code Builder", not the Macro or Expression builders.

Then paste in the code in between the Private Sub & End Sub lines.
Oct 14 '08 #7
sillyr
37
Hi- Ok I put the code into the code builder in the on Key Up in the property sheet for text486, but nothing is happening for the text490 box on the table. The haul number entered from text box486 is going to the correct location on the table, but the record in the other table is not getting the same number.

Option Compare Database

Private Sub txt486_KeyUp(KeyCode As Integer, Shift As Integer)
Text490 = Text486.Text
End Sub

Thanks Sally
Oct 18 '08 #8
Delerna
1,134 Expert 1GB
Hmm, at this stage I would start some basic code debugging.
Since I can't do that I suggest you try that

Do you know how to set breakpoints in your code.
Set breakpoints on the code and then run the form
When/if the code beaks hover the mouse over the textbox name
and its contents will be displayed.

If it dosn't break at the breakpoint, that means the onkeyup event is not being called for some reason.

See if you can determine
why its not working
or
more info and post back here

using these basic debugging techniques. Help documents have more on
debugging basics


QUESTION
Are you using a form/subform design
Try putting an
OPTION Explicit into your code after the OPTION Compare Database
This will force the interpretor to report undeclared variables

I bet Text490 will be reported as an undeclared variable because it exist on the subform and not on the form (where the code is)

Check up "How to reference controlls on a subform" in the help documents.
Ask if you need help.
Oct 19 '08 #9

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

Similar topics

2
by: Bob Cottis | last post by:
I am getting wierd behaviour with IsNull in ASP. I am passing a string (which may be null) to a function. When the string is null, IsNull seems to return false the first time it is called, then...
2
by: Trev | last post by:
I have two tables, tblMTO and tblIMPORT_MTO. If I import an entire MTO into the import table I want to create a delta from it (i.e. leave only the changed items). I have a view (simplified) ...
6
by: Martin | last post by:
Dear Group Just wondered how I can avoid the CHAR(32) to be inserted if @String1 is NULL? SET @String3 = ISNULL(@String1,'') + CHAR(32) + ISNULL(@String2,'') Thanks very much for your...
6
by: Eric J Owens | last post by:
TIA! I recently moved some forms from an a2k mdb file to an a2k adp. There is now an error when opening one of the forms 'the isnull function requires 2 arguments', but I only find references...
4
by: Paul Spratley | last post by:
Hi all Firstly this my first time posting to technical groups - so any mistakes I apologise for in advance. I am trying to count records in several secondary tables for the same run in a...
2
by: Raoul Watson | last post by:
I have used isNull statement for as long as I have used VB.. Recently I am devugging a program and it is very clear that the "IsNull" function sometimes would return a true even when the value is...
16
by: madeleine | last post by:
Please can someone help me, I think I may go mad with this one: Do While Not IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate))) If IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))...
2
by: Hexman | last post by:
Hello All, In SS EE I have nulls in a column. I want to select and eventually change to a zero (its a smallint column). I've tried selecting 'null', 'dbnull', etc. Then I read about the ISNULL...
4
by: jimm.sander | last post by:
Hello, Problem: Im using isnull() in vbscript to determine if a field returned from a ado object call is in fact null. The problem is when I use isnull in anything other than a response.write()...
16
kcdoell
by: kcdoell | last post by:
Hello: When I write new record I have the following code: Private Sub Form_BeforeUpdate(Cancel As Integer) Me!RowIsActive = False 'When a user is creating a new record the following...
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: 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...
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: 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...
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...

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.