Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95

    Unanswered: different tables in one form

    Hi.

    I have a form, based on a 'main'-table: It contains information about connections (wallpatches) to rooms and what kinds of computer or telephone are connected to them. I have a form, with 2 navigationbuttons (next and prev) which let me navigate nicely through this one table, displaying info in a few textboxes (such as the patch-code, recordnumber, telephone-number).

    However, I have other tables linked to this table (such as roomnumber and pc-specifications) and I'd like to be able to display this information too, but I cant figure out how. A form is based on one table, so how do I 'connect' a txtfield on this form to connect to info from a 'linked' table?

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Use either subforms or a query...

    In the query you can pull all the information into one "virtual table" and show that information on the screen.

    The subform is then simply based on the table you need and linked using the subform window.

    Note: You should allways base your forms on query's, in particular when building an app that will be input by users. This way you can much better control who goes where security wise. Should you ever need security that is....

    Regards

  3. #3
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by namliam
    Use either subforms or a query...

    In the query you can pull all the information into one "virtual table" and show that information on the screen.

    The subform is then simply based on the table you need and linked using the subform window.

    Note: You should allways base your forms on query's, in particular when building an app that will be input by users. This way you can much better control who goes where security wise. Should you ever need security that is....

    Regards
    k so if I understand correctly, I can just make a SQL-statement where I get the desired result and then assign that result to the txtfield?

    Or should that txtfield be in a subform per se? That would mean for every different table I should make a subform??

    btw, it should be very simple, its like this: I have txtboxes showing patch-no, phonenumber, PC#, and theres other boxes with REFERENCES to other tables, f.i. it says

    patch BB032
    Phone 8923
    PC# 10326
    PC-spec 1

    I want to replace the 1 in PC-spec with "Dell Optiplex gx260", info I have stored in another table. So it should be something like: SELECT pcinfo FROM tblpcinfo WHERE id = currentrecord
    or something along those lines. :s
    Last edited by avlan; 02-16-05 at 06:54.

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    The easiest way for something like this is to use a combobox which will lookup the value (automaticaly) in the second table

    See the sample in the attached A97 db.

    Regards
    Attached Files Attached Files

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dependiong on the amount of 'other' information you have to display then combo, or list boxes could be a satisfactory solution. However the display will be linked to a very limited amount of information, and probably not a suitable method for data capture.

    The other alternative is subforms, stylistically it depends on what you prefer and how much data you have to display.
    You could either have a subform(s) visible all the time, or used a tabbed dialogue, clicking the tab shows a different form.

    A word of caution though to many sub forms in the screen can confuse the viewer, and to many nested subforms WILL confuse the viewer. Tabs are an effective way of simplfying a complex interrealtionship whilst limiting what is displayed at any one time.

  6. #6
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Hm that IS what I'm looking for, I just don't understand how it works yet checking

    -edit-

    I dont get it
    You have a form, idd based on table1, and then you add a combo-box witht the line "SELECT [Table2].[theNumber], [Table2].[TheValue] FROM Table2; ", and somehow this is linked to Table1, although you dont have a relationship between the two tables, nothing.

    How does this combo-box 'know' what record is selected and how table2 is related to table1???

    -edit-

    o crap... source Still, it dont work in my own db

    -edit-

    Yes it does. Thx, that was what i was searching for. One last question: Can we make a combo-box look like an ordinary textbox? I just want to show the value, not the whole list of roomnumbers... So I mean without the dropdown-thingy ( I can lock it etc. but I want to remove anything that might cause confusion)
    Last edited by avlan; 02-16-05 at 07:45.

  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    If you just want to display, without any edit option what-so-ever then the best way to go is to make a query in which you do something like below SQL (to try in my sample db)

    SELECT Table2.TheValue
    FROM Table1 INNER JOIN Table2 ON Table1.anynumber = Table2.theNumber;

    Regards

  8. #8
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by namliam
    If you just want to display, without any edit option what-so-ever then the best way to go is to make a query in which you do something like below SQL (to try in my sample db)

    SELECT Table2.TheValue
    FROM Table1 INNER JOIN Table2 ON Table1.anynumber = Table2.theNumber;

    Regards
    Yeh at least for some field that is what I want, and I understood this is the safest way anyway. With the comboboxes I have to find a way for the user to select the one they want without messing up the database.. How Access does it I dont know, but if I change a combobox it stores the new value on the currentrecord (in stead of what i want: currentrecord is reset to the new/selected value, changing all other fields with it)

    thx for the tips guys

  9. #9
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    How Access "does it" is that the number is hidden but appointed as the "bound column"
    Thus the number is looked up in the combo box and then displays the text given for that number. However if you change the text, it will (inside the combobox) also change the number....

    I hope that more or clearing it up for you.

    Regards

  10. #10
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by namliam
    How Access "does it" is that the number is hidden but appointed as the "bound column"
    Thus the number is looked up in the combo box and then displays the text given for that number. However if you change the text, it will (inside the combobox) also change the number....

    I hope that more or clearing it up for you.

    Regards
    Yes thx it helps a lot, I'm starting to understand the 'way of thinking' access uses

    Another question: If I lock and de-activate that combobox, then there is no way a user could mess it up, right? So... in that way, a bound combobox can be acceptable? It would then be a read-only (show-only) field...

  11. #11
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    If you lock it there is no need to disable it or v.v.

    But yes.... genaraly its true... But if "average" users see a combo the will want to click it (my experience)

    Regards

  12. #12
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by namliam
    If you lock it there is no need to disable it or v.v.

    But yes.... genaraly its true... But if "average" users see a combo the will want to click it (my experience)
    Agreed, and I haven't found a way to 'not show' the dropdown-marker so I'll probably make it a textfield after all...
    I've just managed to make unbound textfields showing the related info when a roomnumber is selected in a dropdown \o/\o/\o/ so I'm getting there

  13. #13
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Ok, next problem: I can locate certain data through the findfirst-method, works fine, but now I have the problem that on most occasions there are 2 or sometimes even more 'hits', and I wanna show them too. I know there is a thing called 'findnext', but I have no idea how to use it or how/what it returns, let alone if this is what I could use.

    So: 1 table, containing officenumbers (or rather references) and corresponding employees. I have officenumber 9, 'containing' 3 employees: How do I 'print' them to my form (in textfields or whatever)?

  14. #14
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    For this you can use either a list box (unbound) with a select statement or (and i prever this one cause its easy ) Use a table like subform and link it to the main form.

    Thats probably as clear as mud, but try to figure it out. If you cannot, post back....

    Regards

  15. #15
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by namliam
    For this you can use either a list box (unbound) with a select statement or (and i prever this one cause its easy ) Use a table like subform and link it to the main form.

    Thats probably as clear as mud, but try to figure it out. If you cannot, post back....

    regards
    One step back, how do I use a variable in this? I know how to use a SQL-statement for a combobox, I know how to use a query for a subform, but how do I make that query dynamic (SELECT name FROM table WHERE roomnr = "; &var )... Just code this in a module again? But how do I code it such that the output is written to a combobox?

    Core of my problem (in understanding) is how to control a public function that returns multiple results (in stead of findfirst: 1 result), is it even possible that a function returns multiple values and if not, THEN how do I get a collection of results returned and displayed in a combobox?

    Pretty basic I guess, I'll go dig through some tutorials again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •