By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,478 Members | 1,604 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,478 IT Pros & Developers. It's quick & easy.


P: 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

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
Share this Question
Share on Google+
8 Replies

Expert 100+
P: 1,134
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
  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
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
2) change the names for your textboxes to match the names in my code

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

P: 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

Expert 100+
P: 1,134
why is there an = sign before Private?

no error

error no operator
Oct 13 '08 #4

P: 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

Expert 100+
P: 1,134
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

Expert 100+
P: 378
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

P: 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

Expert 100+
P: 1,134
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
more info and post back here

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

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

Post your reply

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