[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can i create one form to use with multiple tables?

Posted on 2009-04-17
16
Medium Priority
?
317 Views
Last Modified: 2012-06-27
Hi I am creating one form, but have three tables which I work one. Is it possible to create say a drop down box to select which table to draw data from and work on?
0
Comment
Question by:heijmer
  • 4
  • 3
  • 3
  • +3
15 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24166288
What is your intention here. Do you want to perform updates or are they all read only operations

If all 3 tables have the same spec i.e. number of columns, column names etc then yes, what you say can be done simply by changing the form's recordsource

If they are different of course, it makes sense to create three different forms. You can always use the form wizard and create default forms to start with then you can tweak them to your heart's content!
0
 

Author Comment

by:heijmer
ID: 24168773
Hey

Thanks - all three tables have the same layour and feilds - just different data. So yes I could choose changing the forms record scource manually in design mode on the form.

But the form is being used by my staff who won't know how to do this, and so I was wondering if I could create a drop down box to select which table to work on?

Thanks
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24168995
ok, try this then

create a combobox, call it cboTables and make it a value list so you add in your own values. The values of course would be the tanlenaes eg Table1;Table2;Table3

Now using the afterupdate event of this, try changing recordsource
eg

private sub cboTables_afterupdate()
    if nz(cboTables.value,"") <> "" then
        'save any outstanding changes
        if me.dirty = true then me.dirty = false

        'update recordsource
        me.recordsource = cboTables.value

        'requery
         me.requery
    end if
end sub



give it a go, not sure if when u close it, it will prompt you to save changes, due to recordsource change
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:heijmer
ID: 24195311
Hi tried this one,

and although it didn't through up any error messages - it does not do anything when I select the table name on the combo box
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24195429
can u step thru this code? or if you are not familiar with that, use msgbox
eg

private sub cboTables_afterupdate()

msgbox "In cboTablesAfterUpdate 1"

    if nz(cboTables.value,"") <> "" then
        'save any outstanding changes
msgbox "In cboTablesAfterUpdate 2"
        if me.dirty = true then me.dirty = false

        'update recordsource
        me.recordsource = cboTables.value
msgbox "In cboTablesAfterUpdate 3 rs is now >" & me.recordsource & "<"

        'requery
         me.requery
    end if
msgbox "In cboTablesAfterUpdate DONE"
end sub


go to forms design view, then look at properties, from the dropdown, ensure you have selected Form, then click on the events tab and see there is an entry for afterupdate
0
 

Author Comment

by:heijmer
ID: 24203081
Ok,

I am a complete novice when it comes to programming, and would be grateful for a set by step guide!

I have set up three tables called 42,43, and 65 (which refer to my three branch codes)
In each table i have created a lookup field called cboTables

Then in the form I have added a combo box with a drop down list of my three tables names.

The form record scource is currently table '65'

I have copied your coding into an event procedure under the cbo table combo box on the form

And nothing is happening!!!!!!
0
 
LVL 85
ID: 24390791
Add a combo to your form. Do NOT set a controlSource for this combo. Set the Combo's properties as such:

RowSourceType = "Value List"
RowSource  "42,43,65"
ColumnCount=1
ColumnWidth=1
ControlSource = <nothing>

In the AfterUpdate event of that combo:

Me.Recordsource = "SELECT * FROM " & Me.NameOfYourCombo
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 24392531
Hi guys

I had a bad experience with objects that start with numbers, Access was acting strange.

So I would say, change the table names to tbl_42 , tbl_43 , and tbl_65, then go on with the suggestions of either rockiroads (but you have to set the combobox RowSourceType and RowSource like LSM proposed) or LSM's.

jaffer
0
 
LVL 2

Expert Comment

by:Joeyen5
ID: 24396943
Another option may be to use Subform.  You can create 3 subforms pointing to each of your tables.  Then you can embed the subform in your main form  Or what I have done in the past is to create tabs for each subform/table referenced.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24397453
I'm just trying to figure out why 3 different tables?

Add a column that is the Branch_Cd -- append the tables tables together and then it is a matter of just filtering on the Branch_Cd. When doing reports/data mining do it the same way.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 24397510
That is a nice one jim,
he can use UNION query, where he can add all 3, then like you mentioned, filter it.

jaffer
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24397591
I'm suggesting 1 table and normalization.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 24397654
Yeah I know, but that brought the UNION query idea :)
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 24398453
heijmer. what is the name of your combo on the form, is it called cboTables?
also the event may not be registered

can u do this

go to forms design view, the click on the combo. Now look at its properties, there should be a series of tabs. Click the Event tab then click on On AfterUpdate. A small button should appear, if u click that, it should do one of two things

1. take u to the code
2. prompt you with a list of 3 things. If it does then click on Code Builder

Now add the code given. Of course, we need to confirm the name of your combo

If it is not called cboTables then rename that to whatever

The following should appear inside your afterupdate code

msgbox "In cboTablesAfterUpdate 1"

    if nz(cboTables.value,"") <> "" then

        'save any outstanding changes
msgbox "In cboTablesAfterUpdate 2"
        if me.dirty = true then me.dirty = false

        'update recordsource
        me.recordsource = cboTables.value
msgbox "In cboTablesAfterUpdate 3 rs is now >" & me.recordsource & "<"

        'requery
         me.requery
    end if
msgbox "In cboTablesAfterUpdate DONE"


You should see the first msgbox appearing. The fact that it does not mean either event not registered or the combo on your form is not called cboTables
0
 
LVL 2

Expert Comment

by:Joeyen5
ID: 24399425
Here's a sample database that uses Tabs and Subforms.
SampleTabsWithSubform.mdb
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question