Full Version: Referencing Other boxes
UtterAccess Forums > Microsoft® Access > Access Forms
keeblerjp123
I have a text box and a box that goes to a table.
That is the best way for whatever goes in that text box to be what ever goes into the box that is linked to the table.
The control source i tried saying =text54 but then it doesnt send the data to the table.
Maks
can u be a litle more especific???
do u want to insert what u typed in the text box to appear in te table???
Jack Cowley
Why bother with two text boxes? Just type what you want in the bound text box and get rid of the other one...
ack
dannyseager
I agree with Jack... but to do it your way you want to add the following code to the after update event on the 1st box.
If not isnull(me.text0.value) then
me.text2.value = me.text0.value
else
me.text2.value = Null
end if
keeblerjp123
Well this is what i have in the (unbound) text box, if it will work inside the table text box then that would be fine.
IIf([ReKey1]=True,[Number of Keys],0)
Jack Cowley
Try this in the On Current event of the form:
If ReKey1 = True Then
Me![TextBox2] = Me![Number Of Keys]
Else
Me![TextBox2] = 0
End If
I am not totally sure what you are trying to do so this is my best guess to a solution...
hth,
Jack
keeblerjp123
works great, what is the best way to envoke an auto update when you click. Right now you wait for the F9 key to be pressed or the box to be clicked in and out of.
Jack Cowley
You can use code like I gave you in the After Update event of the Checkbox. When the user checks or unchecks the checkbox the code executes and updates the textbox that you want filled in.
th,
Jack
keeblerjp123
Is there a code that can do the F9 function when ever you tab to the next cell. The afterupdate code works but only when you apply focus to the box that it is updating.
Jack Cowley
If you are using a CheckBox then code in the After Update event will fire every time you check or uncheck the CheckBox. If your code does not update until you set the focus to the control that you are updating in the After Update event of the CheckBox then I suspect that there is an error somewhere. If your code relies on a value in another control then be sure there is a value in that control in your After Update event and if not then warn the user and cancel the code.
th,
Jack
keeblerjp123
See if this helps
Jack Cowley
Put this code in the After Update event of check box 'ReKey1':
CODE
If Me.ReKey1 = True Then
Me.ReKey2 = DLookup("[Number Of Keys]", "[Key File]", "[Location]= '" & Me.Location & "'")
Else
Me.ReKey2 = 0
End If

Remove the After Update event in the ReKey2 text box.
Jack
keeblerjp123
This is for the same question above, but this time with a text box.
I have a combo box where users select one of 5 words those words filter out the selection of what you can choose in the next box. But i am having an update problem. Right now you have to push F9, for the box to refresh with the right words.
Jack Cowley
Try:
e.NameOfControlToRequeryGoesHere.Requery.
Put it in the After Update event of the combo box.
hth,
Jack
keeblerjp123
Ok, it works, but only in when the subform is by itself, it does not work when you use the main form with the subform inside of it. This is what I have:
Private Sub Flawless_5__AfterUpdate()
Me.Subject_.Requery
End Sub
Jack Cowley
You have lost me. Do you have a control on a form named "Subject_"? What is the underscore for? Is the control on the subform or the main form? If it is on the subform are you trying to requery it from the main form?
need more details on what it is you are trying to do....
Jack
keeblerjp123
Yes i do have a text box named Subject_ i dont know why the underscore is there accept for the shear fact that if its not it doesnt let you choose requery. The control is on the subform and only should reference the subform. When you have the subform by itself the requery works with no problem but when you combine everything together into the form, thats when it produces the error as if the command doesnt work. I hope this clears it up.
Jack Cowley
Your control name as a space or other non-printing character as the last character in its name and that is why you have to use the underscore.
Try this:
[Forms]![MainFormName]![SubFormControlName].Form.Subject_.Requery
I hope that this does the trick for you. You may want to look at this article for how to refer to objects on forms/subforms/subsubforms....
Jack
keeblerjp123
Ok, it could be that its Friday and my brain is now mush, but i am not sure what to do with the
Forms]![MainFormName]![SubFormControlName].Form.Subject_.Requery
I am not sure why everything works in the subform when you open it by itself there is no linking to the main form.
But the subform that just sits inside the main form no longer works.
The reference that is not working is not the requery its the control within a sql query indide a Subject Tect Box. Early post have a Box that has a criteria on it [Forms]![MultipleEntrySubform]![Flawless5*] this is the exspression that errors out and says its not there, but it is there, because it works when the subform is by itself.
Jack Cowley
You say, "... its the control within a sql query indide a Subject Tect Box." Do you have an SQL statement that refers to a control on the subform? What is purpose of this SQL statement? Is the data in the control criteria for the SQL statement?
I am not following what it is that is not working. Can you post your db so I can take a look? (Compact and zip and 500k max size.) No guarantees that I can solve the problem but I will take a look at it.
Jack
keeblerjp123
Ok, iam attaching this as two different file.
If you go into the Multiple Entry Form, go down to where it says hit, type hit then, Tab type STAR, then Tab See if it errors out on you. It is only supposed to pull the STAR ref.
You will have to relink these on your end. Thanks for your help.
Second, any suggestions with making things faster, or the normilizations rules.
keeblerjp123
here is the second part with all the forms.
Jack Cowley
I did not get the front end as it was not attached to your second post...
I have taken a quick look at a couple of your tables and there are things you should change before you go any further. Here is a list:
1. No spaces in field names.
2. Date is a reserved word in Access and should not be use for a field name.
3. Do NOT use the asterisk (*) in the name of a field as the asterisk is another reserved character in Access and will confuse Access.
4. I am not sure what the relationship is between "Entry Table" and "Entry TableMain" but the relationships should be on the Primary Key in one table and the Foreign Key in the other table, not as you have them now.
5. Employee table as full name in First Name field. I am sure you know that this is wrong.
6. "commenttbl" has repeating groups and these should be in a related table.
7. In table "4subject" the Fab5 field should be the FK to the "3Fab Five" tables PK.
8. In table "6location" you have categories like Austin1, Austin2, Austin3, Austin1-2, Austin1-3. I am not sure what these mean but if Austin1-2 means Austin1 AND Austin2 then you need to rethink this table and what the table it relates to.
9. Do not use the "/" character in field names.
Once you have fixed the above then join your tables in the relationship window. Once you have all your structure set up properly then you can start working on your forms. I think you have the cart before the horse here and that is part of the problem with your forms....
hth,
Jack
keeblerjp123
Ok this is the new backend database labeled htmsfl_db.
keeblerjp123
and this is the front end Cs Hit and Miss, which was there befor now adjusted abit.
Please go to the multiple entry sub form, dont worry about group name, go down to hit enter hit then next box enter STAR then try going to the next box. the weird thing is if you were to open up that subform the thing works under subject.
Jack Cowley
For the combo box "Subject*" on form "MultipleEntrySubform" delete the SQL statement in the Row Source then copy and paste the code below:
CODE
SELECT [4Subject].[CategoryID], [4Subject].[CategoryName]
FROM 4Subject
WHERE ((([4Subject].[Fab5])=[Forms]![MultipleEntrys]![MultipleEntrySubform].Form![Flawless 5*]));

You did not correctly refer to the subform in the criteria line. For information on how to correctly refer to forms/subforms/subsubforms take a look here. Also, you need to add this code to the After Update event of the combo box "Flawless 5*":
CODE
[Forms]![MultipleEntrys]![MultipleEntrySubform].[Form]![Subject*].Requery

O STRONGLY suggest that you follow my previous suggestions and remove the asterisks and spaces form field names in your tables as well as properly joining your tables in the relationship window.
Good luck!
Jack
keeblerjp123
Jack any suggestions on this message i keep getting,
Either the expression is typed incorrectly or it is to complicated"
I am taking your suggestions and going throught and removing the * and the spaces. There is only one table i cant do that to and that is 1Group because it is imported information that the first line comes out that way.
It seems to be ok, till you put the requery in there and then it goes error on me.
Jack Cowley
You should be able to create your 1Group table with the correctly named fields and then import the data into that table. Do you get the error when importing the data? Will the importing be something that you do on a regular basis or is it a one time thing? Does some of the data import correctly or do you get the error when starting the import?
I have no idea what is going on so I am not sure what to suggest at the moment.
Jack
keeblerjp123
Ok, I am back to this one.
I have attached the database in its simplest forms.
There are two main forms on there One = Entry Table the other = MultipleEntrys
There is a subform on each one.
When the user picks one of the 5 choices from Flawless its supost to filter out SubjectFab
Anyhelp would be greatly appreciated.
Jack Cowley
Look at Form1 and you will see how I got the combos to 'synchronize'. Also, look at your table 4Subject as I got rid of the name so you see what is actually stored in the table. If you look at your form you will see you have bound the name in the first combo box not the CategoryID, which is what is stored in the table.
th,
Jack
keeblerjp123
Is there no way to due this without using the unbound text boxes.
Jack Cowley
You can do it with bound text boxes. I just created that form so you could see the code I used in the query and in the After Update event of the first combo box.
ak
keeblerjp123
If a subform is within a form, will this work [Forms]![SingleEntrySubform]![Flawless5]
Because right now the answer is no.
Jack Cowley
No, it will not work, as you have discovered, if that is the syntax in the criteria line of the query. You need to refer correctly to the control on the subform. You will find this article helpful for the syntax required when referring to controls on a subform...
Forms]![MainFormName]![SubformControlName].Form.ControlName
hth,
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.