Autopop text box based from the value of another text box

[xygrax]

Hello,


I need a little help in doing my form.


I am working on a text box and I want to display the associated value of whatever entry from textbox which record source is another table.


ex.


if textbox 1 populates "Coke", textbox 2 should display the amount of "$1"


note that the data being displayed in textbox 1 is from another table and the value of that item is also coming from another table.


I also added an attachment for better understanding of what do i want to happen


Thank you in advance.

 

[Mark_]

Before any other advice, I would suggest reading up on data normalization. Any time I see "FieldName 1" (such as your Promo CTE 1 to 6, CTE package 1 to 6, or even mobile 1 and mobile 2) I question if your data has been normalized. Most often you will do a lot more work for no benefit if your database is not normalized and many solutions are difficult to implement.

That said, where is the data you wish to auto populate coming from and how can you tell what should be retrieved? If you have specific criteria you can use DLookup. If it is related to one other field that is being controlled by a combobox, you can include your value in the datasource for the combobox and copy over the related value.

 

[xygrax]

Before any other advice, I would suggest reading up on data normalization. Any time I see "FieldName 1" (such as your Promo CTE 1 to 6, CTE package 1 to 6, or even mobile 1 and mobile 2) I question if your data has been normalized. Most often you will do a lot more work for no benefit if your database is not normalized and many solutions are difficult to implement.

That said, where is the data you wish to auto populate coming from and how can you tell what should be retrieved? If you have specific criteria you can use DLookup. If it is related to one other field that is being controlled by a combobox, you can include your value in the datasource for the combobox and copy over the related value.

Thank you so much for the reply Mark. I guess my answer is I honestly don't know if the data has been normalized since i don't have a clue on how to do it. I'm sorry but how will i do that?


Thanks in advance...

 

[xygrax]

Before any other advice, I would suggest reading up on data normalization. Any time I see "FieldName 1" (such as your Promo CTE 1 to 6, CTE package 1 to 6, or even mobile 1 and mobile 2) I question if your data has been normalized. Most often you will do a lot more work for no benefit if your database is not normalized and many solutions are difficult to implement.

That said, where is the data you wish to auto populate coming from and how can you tell what should be retrieved? If you have specific criteria you can use DLookup. If it is related to one other field that is being controlled by a combobox, you can include your value in the datasource for the combobox and copy over the related value.

But if you mean, did i assign the correct control source on each field, my answer is, Yes. some of the fields are really blank since i didnt enter any value on some of it.

 

[xygrax]

Hello,
I've used DLOOKup but i guess, it's not working for me.


I'm not sure though if i'm doing it right but below is the code i used.


=DLookUp("SpecialRate","tbl_Coursecode","CourseName=" & [txtCTE1])

 

[isladogs]

I expect the space in coursename is a typo.
If not you need to enclose in []

Please use the code tag button (#) to avoid issues and make code more readable.

As I'm sure coursename is a text field you need text delimiters

=DLookUp("SpecialRate","tbl_Coursecode","CourseName='" & [txtCTE1] & "'")

 

[mike60smart]

Hi

Can you upload a zipped copy of your Db??

 

[xygrax]

Hi

Can you upload a zipped copy of your Db??

Hello Mike,


Thank you for your reply. Attached herewith is the zipped copy of my db. Thank I honestly dont know if im doing it correctly. hehe.


What i want to do in sf_UAQStudent form is to display the corresponding amount of whatever is in the Course to Enroll 1textbox to the Promo CTE1 textbox where it data is located in the Course Code Table under SpecialRate column. same goes with the other pomo textboxes.



Been spending the whole night to figure that out. haha


Thank u in advance.

 

[isladogs]

To make life easier for end users, I suggest using combo boxes to make text input easier
In the attached I've changed txtCTE1 to a combo with 3 columns:
CourseID (bound), CourseName (displayed) and SpecialRate (hidden)

The textbox CTE1 is then set to =[txtCTE1].[Column](2) which is the corresponding SpecialRate. Done!

See attached

I would suggest using the same approach for each of your CourseToEnrol boxes
Having said that, you have a very badly designed tbl_UAQStudents table which is far from being normalised.
Good database design will never have fields like PromoCTE1 thorugh to PromooCTE6 and similarly with CTEPackage, RTCN, PaymentDate etc
With this structure, queries will be more complex than necessary & if you need to add a 7th item later everything has to be redesigned. Then if you need an 8th item, you start again ... ad infinitum.
Strongly recommend a redesign

 

[Mark_]

On what database normalization is, you would want to do a search on line. I'd suggest reading through the wiki entry first so you at least have an idea what you are looking for.

Basically it means having only ONE place to store things and keeping like data together. As is, with CTE being used multiple times you will need to check EVERY field that COULD have a value when you are looking for a specific value. As Colin pointed out, if you need to add another, you need to rework your table, your queries, your forms, and your reports.

If you have a table for CTE entries related to students you will have a much easier time showing only those CTE values relevant to a student (and not show black spaces) while also making it much easier to find all students who have a given CTE value. Likewise if one student has more than 6 values you do not need to make any changes.

 

[xygrax]

Hi

Can you upload a zipped copy of your Db??

Thank u Mike