How to get data into textboxes

anb001

Registered User.
Local time
Today, 10:35
Joined
Jul 5, 2004
Messages
197
I have a table with following columns (+ example of data):

TankID TankName CoverID Marking WhereToFind
Frame
F042 FO Tank Hold 4 S 22 402-405 F042-SB-FO-22 Catwalk aft of hatch 4 SB
F042 FO Tank Hold 4 S 24 402-405 F042-SB-FO-24 Cargo hold 4 SB

On a form I have a combobox, with all TankID's shown. The idea is that when I select a specific TankID, then the data for that specific tank should be shown in some text boxes on that form. As each tank has between 1-10 covers, I have "10 rows" of text boxes, like this:

txtCoverID_1 txtMarking_1 txtWhereToFind_1
txtCoverID_2 txtMarking_2 txtWhereToFind_2
txtCoverID_3 txtMarking_3 txtWhereToFind_3
txtCoverID_4 txtMarking_4 txtWhereToFind_4
etc

I would very much appreciate if somebody can assist with the code needed for the combox 'afterupdate' event, in order to do what is descibed above.

Should additional information be needed, please let me know.

I'm unfortunately not able to attached any file, as I'm currently in an 'un-civilized' part of the world, with very limited access to the net.

Best regards
Anders
 
The first problem I see is that maybe you have a normalization issue. it sounds to me as if you need another table in there.


TblTanks
TankID - PK, Autonumber
TankName

TblCovers
CoverID - PK, Autonumber
CoverName
TankID

Unless a cover can go on more then one tank then you need another table.

tblTankCovers
TankCoversID - PK, Autonumber
TankID - FK, number
CoverID- FK, number

Link the tables in the relationship window.

You might be able to do this same sort of thing with other fields. Read up a bit on normalization.

Then when you create a form, you'll create a form for tblTanks, with a subform for the covers (which will show all the covers associated with that tank ID., and put the other fields where they belong (whichever table, tanks or cover). Then on your main form you can use the combo wizard to build a text box to do what you want, select a tank, then the subform would automatically display whichever tank is selected without any extra coding on your part.

I hope this give you a starting point. And if I'm totally off base, about where you are at, my apologies.
 
Well, let me start off by giving the token "Normalization" line :)

The relationship between the Tanks and TankCovers is what is called a One (One Tank) to Many (Can have Many covers) Relationship. With that relationship, you should really have two tables. One for the Tank information and one for the each Cover. You would store the TankID in the TankCover table. This way, you can reference all the covers via the TankID. This also allows for down the road when you might need to record data on a Tank that has more than 10 covers. With your current setup, you would have to alter the table to store that data. Lots of headaches.

Now, if you are absolutely, positively, beyond a shadow of a doubt never, ever going to have more than 10 covers and want to proceed with your table setup you can (I strongly advise against it though)

You would include the fields with the tank cover data in the row source for the combo box. You can then reference those columns in code. For example:

Code:
me.txtCoverMarking1 = me.cboTankComboBox.column(x)

x is going to equal the column that has the data for the CoverMarking1. Remember that columns in combo boxes and list boxes start the numbering at 0.

Include the
 
I should probably have explained a bit more about how my tables are made. Sorry about that. What I mentioned above is actually a query, getting its data from two tables. A mistake from my side.

These tables are:

-tblTanks-
TankID (this is a unique field)
TankName
Fame
TankType
HoldNumber
TankRemarks

-tblCover-
TankID (same as in above table, but not unique - linked with TankID above in a query) CoverID (unique field) WhereToFind Marking CoverRemarks

A specific cover only gives access to one tank. The autonumber part can't be used, as Covers and Tanks have specific ID's. They are each unique, but they ar e a mix of letters and numbers.

If further information is needed, please let me know.

Best regards
Anders
 
I should probably have explained a bit more about how my tables are made. Sorry about that. What I mentioned above is actually a query, getting its data from two tables. A mistake from my side.

These tables are:

-tblTanks-
TankID (this is a unique field)
TankName
Fame
TankType
HoldNumber
TankRemarks

-tblCover-
TankID (same as in above table, but not unique - linked with TankID above in a query) CoverID (unique field) WhereToFind Marking CoverRemarks

A specific cover only gives access to one tank. The autonumber part can't be used, as Covers and Tanks have specific ID's. They are each unique, but they ar e a mix of letters and numbers.

If further information is needed, please let me know.

Best regards
Anders

Well, if the data you are looking to display about Tank Covers is already in the query and the form is bound to that query, you can just add the fields. Alternately, you could use a form/subform to display all the data about all the particular tank cover data. The main form would contain the Tank information, the subform would contain the tank cover table and you link them via the TankID.
 
Thank you for the replies so far. However, I still haven't got a clue how to solve this. I'll try to illustrate a bit more - maybe that will help. I have just included some of the fields, in order to try to keep it simple.

-qryTankAll- (+ some data examples)

TankID TankName TankType CoverID Marking
F042 FO Tank Hold 4S Fuel Oil 22 F042-SB-FO-22
F042 FO Tank Hold 4S Fuel Oil 24 F042-SB-FO-24
WB122 WB Tank Hold 12-13 S Ballast 114 WB122-SB-WB-114
WB122 WB Tank Hold 12-13 S Ballast 116 WB122-SB-WB-116
WB122 WB Tank Hold 12-13 S Ballast 132 WB122-SB-WB-132
WB122 WB Tank Hold 12-13 S Ballast 136 WB122-SB-WB-136

In form [frmViewInfo] I have a combo [cboTankID]. The 'Row source' for that combo is:

SELECT DISTINCT qryTankAll.TankID FROM qryTankAll ORDER BY qryTankAll.TankCode;

My plan/hope is that if I select e.g. 'WB122' in the combo, the text boxes on the same form, will have following data in them:

txtCoverID1 Marking1
114 WB122-SB-WB-114

txtCoverID2 Marking2
116 WB122-SB-WB-116

txtCoverID3 Marking3
132 WB122-SB-WB-132

txtCoverID4 Marking4
136 WB122-SB-WB-136


Had it been only one text box set, I could have used the 'DLOOKUP' function, however that doesn't seem to work in this case.

Could the 'FINDFIRST', 'FINDRECORD' and/or 'FINDNEXT' functions be used? Meaning that using the 'FINDFIRST' would insert the first record for the selected tank, into the first set of text boxes, and then the 'FINDNEXT' a numerous of times for the next sets of text boxes.

How would the coding be?

To answer a question, from a previous post, the the maximum number of covers that one tank has, is 10. No more can and will be added (that is as such not allowed).

Best regards
Anders
 
The easiest way would be to have a subform that has the data you want to display...and link it via the Combo box (Parent) and TankID (Child). You can use a continuous form.

Populating text boxes would be a pain in the butt, because you can create text boxes on the fly. So, you would have to create 10 sets of text boxes, then figure out how many of those sets you will need, loop through the recordset to grab the info to populate the text boxes.

I'm getting ready to leave, but I can do up a quick demo based off of the data you provided and post it tomorrow. (Unless someone else wants to jump in)
 
Scooterbug,

Thanks, a demo of how to populate the text boxes would be highly appreciated. I have 10 sets of text boxes,and it will not be more than that, and whether all will be used or not, depends on the tank selected in the combo.

Rgds,
Anders
 
Alright, here is a demo. Form 1 displays the data in both a subform and a list box.

Form 3 does it this way:

Populating text boxes would be a pain in the butt, because you can create text boxes on the fly. So, you would have to create 10 sets of text boxes, then figure out how many of those sets you will need, loop through the recordset to grab the info to populate the text boxes.

and I made a typo, it should of said CAN'T create text boxes...
So, I put 10 text boxes for both CoverID and Marking. In a nutshell, the code behind the after update event for the combo box sets the visibility of the text boxes to false (The boxes are called txtCoverIDx with x being 1-10). It then opens a recordset, counts the number of records, then cycles through them. As it cycles through, it sets the visible property to true and puts the value into them.

Me personally, I would use the list box option...i'm a list box junkie :)
 

Attachments

Hi Scooterbug, on behalf of my husband I would like to say thanks until further, he will revert asap. Rgds, "the wife"
 
Scooterbug,

It worked like a charm. Thanks a million.

I used ´Form3´ (the one with the textboxes). Exactly what I was looking for.

I have a couple of additional questions, though.

1. Some of the fields in the table are empty, as it is not a requirement for them to contain any data (it is additional fields, which can contain Special Instructions and Remarks for the specific covers).
When I run your code, I get a [Run-time error ´94´: Invalid use of Null].

How do I include that it doesn´t matter for some of the fields (textboxes) to be empty?

2. Is there a way I can adjust the height of the form to those textboxes which are visible (my guess is no, because of the invisible textboxes, but I just want to make sure)?

Best regards
Anders
 
1. Some of the fields in the table are empty, as it is not a requirement for them to contain any data (it is additional fields, which can contain Special Instructions and Remarks for the specific covers).
When I run your code, I get a [Run-time error ´94´: Invalid use of Null].

How do I include that it doesn´t matter for some of the fields (textboxes) to be empty?

You will need to have it test for Null. Add the following:
Code:
iCoverID = nZ(![coverID],0)
sMarking = nz(![marking],"")

2. Is there a way I can adjust the height of the form to those textboxes which are visible (my guess is no, because of the invisible textboxes, but I just want to make sure)?

I'm sure it's possible to set the size of the form, but i've never done it, so wouldn't know how to do it. Sorry.
 
Ok, thanks. I consider this solved now.

Rgds,
Anders
 

Users who are viewing this thread

Back
Top Bottom