IsNull

[sillyr]

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.

[Delerna]

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

[code=vba]
Private Sub txtHaulNumber_K eyUp(KeyCode As Integer, Shift As Integer)
txtDiscardHaulN umber = txtHaulNumber.T ext

'COMMENTS
'Notice txtDiscardHaulN umber has no .Text on the end
'If I did that then you would get the error
'txtDiscardHaul Number must have focus
End Sub
[/code]

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,op t,lbl

[sillyr]

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(K eyCode As Integer, Shift As Integer)
Text490 = Text486.Text

[Delerna]

why is there an = sign before Private?


operator=operan d
no error



=Private
error no operator

[sillyr]

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

[Delerna]

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(Ke yCode As Integer, Shift As Integer)

End Sub
but the parameters will be different

Now just add the line
Text490 = Text486.Text
into that subroutine.

[Megalog]

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.

[sillyr]

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(Ke yCode As Integer, Shift As Integer)
Text490 = Text486.Text
End Sub

Thanks Sally

[Delerna]

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.