Tab Control & Tables - Issues!

[MIScjryan]

I work in MIS, and one of my job descriptions is to manage inventory control. I have created an Access dbase for this, and after many wasted hours, I decided that using Tab Control on a single form was the best solution.

There are four categories that I have created thus far (possible expansion in the coming weeks, depending on how this goes): Computer(s), Monitor(s), Printer(s)/Multi-Function Unit(s), and Other. There are a number of fields for each category, such as Serial Number, Asset Tag, Vendor, Model, etc.

I want to have each category associated with its own table, but I can only assign one record source to the form. I tried using just one table, but if I create one record, say, for Computers, and another for Printers, the data shows up on the same record number, and displays the data in the same row of the table...not what I'm looking for.

Any suggestions on how I can go about fixing this Tab Control? Or is there another way to do this on a single form (multi-form didn't appeal to me when I tried)?

 

[roppetech]

For what you're doing, I'd suggest using multiple tables, each in a subform.

You can change the recordsource for the subform anywhere in your code simply by

frmInventory_sub.recordsource = "SELECT * from tblPrinters"
frminventory_sub.refresh

- RoppeTech

 

[Rauken]

Yep create four tabs. In each tab put a subform that covers most of the tab. Create four seperate forms containing what you need then bind them to the subforms.

 

[BSman]

Have a master table to list the owner (or location, if you prefer that as the organizing factor) of each set of equipment. (An owner could be a storeroom, for example.)

This would be in the heading of your form. Each tab would display the appropriate equipment assigned to that owner (or location, if you use location rather than owner).

I designed a similar database, but used one table for the details of the hardware (like serial #, location, owner, type, etc.). Unless there are many different items of data needed for each type of equipment, keeping the inventory in one table that includes a column for hardware type (referred to a lookup table of hardware types) can work better and can easily handle "new" types of equipment. It's also easier to search the data and produce reports. For example, a report of just printers is simple: just set the hardware type to printer, while if you want the hardware assigned to a specific person (or a location), you can easily sort by the person (or location) and print a complete list from the one table of the equipment assigned to a specific person (location).

 

[MIScjryan]

Thanks for the info, folks. I haven't had a chance to try it yet, and the day is almost over. Right now I'm using a simple dbase running four seperate forms off a splash-page form...kinda working on this in the background to re-organize the entire inventory list.

Thanks again, hopefully I'll be able to test it out Monday or Tuesday!

 

[MIScjryan]

It worked! Thanks again!

 

[GaryWilsonCPA]

I like the tab form but have this one issue.

1) I have 4 tabs all being populated by the same table. The table has 50 fields of information that relate to a clients demographics.

I use the tabs to organize the types of demographics.

The problem I have is that when i reach the bottom of a tab, I would like the user to continue to tab2 and continue data input. What is happening is that when i get to the bottom of tab1, it jumps to a new record. I would like it to jump to tab2.

Any ideas on this???

Thanks

 

[willir]

MIScjryan

You may want to review...
A messy application, how do I "start over" to make the data work well?

I feel using tab forms is a great way of managing a complicated inventory.

By using one device master table as the record source for the main form, and then subforms on different tabs for each type of device allows the control needed in handling differences in the specifications. Moreover, you display only the tabs relavent for the specific device.

I am glad you already have a solution but I just thought I would add my comments any way.

... after many wasted hours, I decided that using Tab Control ...

... says it all. Been there - done that. ;-)

Richard

 

[GaryWilsonCPA]

In answer to my question about jumping from tab1 to tab2 after tabbing through the final entry on tab1. I use a macro that whose action is "GoToControl" and then i pick the first field on tab2. I place this macro in the "on key press" properties.

This seems to fit my needs. Just wanted to update, incase someone else has same prob.

 

[willir]

Gary
Usually, we start another thread for a different problem.

That being said...
- Check your keyboard settings. From the menu, "Tools" -> "Options". You have some control over the <Enter> key
- You have more control of the via it's properties. Open the form in design mode and make sure the "Properties" window is open (from the menu, "View" -> "Properties"). Select the form the clicking in the square box in the top left corner where the where the verticle and horizontal rulers meet. Select the "Other" tab in the "Propties" window. You can change the "Cycle" field from "All records" to "Current Record", or perhaps "Current Page".
- Check your TabOrder to make sure it is correct for the form and each tab page.
- You can use EventProcedures such as "OnExit" to automatically move to the next field. Note: Using EventProcedures is a very useful tool, perhaps more so than using Macros. Click on the last control field on the tab page and then select "Events" from the Properties window. Located the OnExit event, select the field and choose "EventProcedure" from the pcik list; then click on the "..." command button that appears to the right.

This will open up the VBA coding window.

The following approach may work for you...
If you want to go to a subform...
Me.NameOfSubform.SetFocus

If you want to go to a control on another tab / page that is not in a suform...
Me.NameOfTextOrComboOrListBox.SetFocus

If you want to got to a tab page
Me.NameOfTabPage.SetFocus

Here is the rub...
You do not know if the user is going backwards or forwards. the above code assumes they are going forwards, or in one direction. How can you tell which way the end user if going?

Well there are a few ways. Most of them involve using a variable that is within "scope" of the entire form. For example, you can use a hidden control field on the form of the current field the end user is in. Then, when the enter the new control field, you can determine if they are going backwards or forward. You can accomplish the same thing by using a variable declared at the top of the VBA module.

Let's assume you choose to use a hidden control on the form called CurrentControl, referenced within VBA as Me.CurrentControl.

You have two tab pages - address and tickler history.

The text box "above" the last control text box on the tab page is Country.

The last text box on the tab page is ZipPostalCode.

The next control will be on the next tab page called Ticklers.

Private Sub Country_Exit (Cancel As Integer)

Me.CurrentControl = "Country"

End Sub


Private Sub ZipPostalCode_Enter()

Select Case Me.CurrentControl 
   Case "Country"
      Me.CurrentControl = "Forward"
   Case "Ticklers"
      Me.CurrentControl = "Backward"
   Else Case 
      Me.CurrentControl = "Forward"
End Select

End Sub


Private Sub ZipPostalCode_Exit (Cancel As Integer)

If Me.CurrentControl = "Forward"
   Me.Ticklers.SetFocus
Else
   Me.Country.SetFocus
End If

End Sub


Private Sub SbfrmTickler_Enter ()

   Me.CuurentControl = "Ticklers"

End Sub

Hopefully, you can see that by using some logic, you can determine the next logical place to set the focus for the user.

Richard

 

[TheAceMan1]

How are ya MIScjryan . . . . .

Good table structure is not only the start of a good database, it also determines how easy you can make modifications, additions & deletions. Said another way:

[blue]Table Structure determines just how much you will/won't pull your hair out tomorrow![/blue]

You may get thru this, but I query if your headed in the right direction . . . .

Be sure to see the reference provided by [blue]Willir[/blue], and at your leisure see the following:

Normalizing Tables Table Relationships

Better to know these things so at least others that will come your way, make sense . . .

See Ya! . . . . . .