Microsoft Access 2000

[vree]

While using Access 2000 for a couple of years now, I am still a high-end newbie, low-end intermediate user.

I have a question, over at

http://www.mvps.org/access/

they have an article about Never using Lookup Fields and why.

I would like to know though, IF you don't use a lookup field, how do you accomplish the task of getting all the info in easily and correctly?

Right now I am moving my data from a 2yr old db and trying to clean up and make a better db...I have Companies in my db, and Companies have different types. In my old db, I used a lookup field to "lookup" to the CompanyTypes table and got the info from a dropdown list.

If I don't do this, I would have to keep a list? of types, and manual enter?
Please enlighten, would love to discuss and learn.

"For a succesful technology, reality must take precedence over public relations, for nature will not be fooled.

 

[willir]

vree

First a star for you for asking a pretty good question. Your first star -- you are now no longer a newbie!

I enjoyed the article, and understood the gist of their arguments. My sense that this applies more to the use of using the LookUp view at the table level, and not so much for the use of combo boxes in a form.

I guess it boils down to relevance, needs and style.

Microsoft tries to make things easy for users, but they seem to often like to things with their own twist.

Ten years ago, who would have thought we would have so many database designers out in the world. The DBA status symbol is pretty high up in the scheme of things in the IT circles -- commands respect and usually oodles of cash. These guys and gals are responsible for systems that have hundreds or thousands of users, and work with systems that handle zillions of transactions, and everyone may think they have wings when they do their job right.

(Oops this should be in the Water Cooler section)

Back to earth... People usually learn fairly quickly that although the spreadsheet is an absolutely wonderful tool, it has limitations, specifically it does not do relational.

So Access came along. More and more users bought into using databases. In fact, in many environments I work with, Access is now the number one tool for sub-systems. (A sub-system is a work around where the business application doesn't cut it.)

However, with the influx of Access users, a lot of poorly designed databased have been invented. The hands-on approach is defintely favoured rather than paying a programmer, consultant or DBA type of person to design and create the database, etc. (I suspect that some of the brighter stars in this forum are consultants, and experience this attitude way too often.)

(Good exmaples of this is the number of queries by people trying to design a database for tracking footbal pools. With system tools designed to handle zillions of dollars now being used to track sports pools -- go figure! Would you pay thousands or tens of thousands of dollars or pounds to create this type of database??)

So my perception is that the "Ten Commandments of Access", and the "8 Evils of LookUp fields in a Table" is advice given by some of the gerus who do have a wealth of experience, education and a proven track record.

For small database applications, it probably does not matter what and how you do things as long as you get the results you need, and data integrity is maintained. If it is ugly, but it works, then that is okay.

When you get to the advanced databases -- lots of records, external links to other database, etc., then it becomes more critical to do things right in a generic fashion where the design, the format and procedures adhere to the industry standards. For example, standards to SQL as defined by ANSI.

Some of the reasoning behind the "evils of lookup fields" are, in my opinion, a preference. It personally does not bother me that at the table level I see the customer name instead of the CustomerID value. As long as the design is right, and the data is solid, it works either way.

But there are some truths.

For example. The ability to create a LookUp combo or list box in the table design seems like a great idea -- saves time, simplifies things for the everyday user. BUT it is not a standard to database design, and other database engines will not support it. Consequently, this feature may cause problems if working in a mixed environment. Ditto if upgrading from Access to an SQL database.

Another example is the use a stored query in a combo box. It simplifies things. But if you need to change things, such as reverse the vender code and vender name, you just made a change to a query that could be used in a dozen other places. As long as you are aware of this, then fine. But it could become a maintenance issue that some other hapless person may have to fix.

By the way...
It is possible to write queries and filters without the use of combo and list boxes. This is how it was down before Access came along. Just a lot more work, and something well beyond the general user out there trying to create a desperately needed database with a shoe string budget.

My bottom line - be informed, and make your choices.

Access is a great tool that has helped many people and business get things done that would have cost a small fortune with the larger applciaitons such as Oracle, SAP, Informix, etc. As with all tools, it has to be used properly or it comes around and bites you.

Richard

 

[WildHare]

vree, I agree with Rich - pretty much everything he says is absolutely on the mark. Deciding 'how to' do things very often comes down to the life cycle requirements, the expected user base, and the maintenance/documentaion issues. There are times when breaking every 'rule' in the book is the right way to do something, because time or money or other factors overrule the correctness requirement. It's just not something I'd recommend making a practice of.

Plus, I gave you another star just for using Feynman's NASA quote...

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[vree]

Thank you all for you input, and kind words!

Oh and by the way,
Feynaman Lives!
vree

 

[vree]

LOL well can't spell today!
Feynman Lives!

 

[vree]

Ok guys, here's the problem, in adopting my new "religion" NO Lookup fields, here's the deal.

tblOrganizations
tblAddresses
tblPeople
tblOrgAddresses
lkuptblOrgAddressType
lkuptblOrganizationType

In my original db, i had a lookup field in tblOrgAddresses, to lookup the correct Address. A Company can have more than one address, based on type.

So, I am attempting to switch to using a form, which in OrgAddresses, you can choose the correct address and update the OrgAddresses table.(Which it does correctly.)My question is, how do I add a new address? with out closing the form, going to the Addresses form or table and adding a new one. I haven't got the hang, of filtering a subform? and don't know if I could do it that way at all.

So, what I want to do is pick the address for a company, and if it is not in my drop down, add the new address.
thanks again
victoria
feynman lives!


vree

http://www.groundworkssoftware.com

"For a succesful technology, reality must take precedence over public
relations, for nature will not be fooled."
R.Feynman

 

[vree]

OK! So far so good, but one error to clear up.
Using the above tbl structure:

frmOrgAddresses
lngORGADDRESSID
lngORGID
OrgAddressType

I put this code in the DblClickEvent of the lngOrgAddressID field:
Private Sub Combo10_DblClick(Cancel As Integer)

On Error GoTo Err_AddressID_DblClick
Dim lngAddressID As Long

If IsNull(Me![lngAddressID]) Then
Me![lngAddressID].Text = ""
Else
lngAddressID = Me![lngAddressID]
Me![lngAddressID] = Null
End If
DoCmd.OpenForm "frmMainAddress", , , , , acDialog, "GotoNew"
Me![lngAddressID].Requery
If lngAddressID <> 0 Then Me![lngAddressID] = lngAddressID
Me![lngORGADDRESSID].Requery

Exit_Combo10_DblClick:
Exit Sub

It kinda sorta works, if you double click the field you first get

Error: Object doesn't support this property or method.

HOWEVER, it will then go ahead, open the MainAddress Form, let you add a new address and updates the Address Table, and the OrgAddress Table accordingly.

Any help with the bug killing appreciated.


vree

http://www.groundworkssoftware.com

&quot;For a succesful technology, reality must take precedence over public
relations, for nature will not be fooled.&quot;
R.Feynman

 

[willir]

vree

From design to coding...

In Access, the table field name can be deliminated with [], ie] [lngAddressID]. You will see this a lot when working with query objects

In Access, Me refers to the current form.

Here is the gotcha...
When creating the form with the Form wizard (which is a pretty cool way to crete a form), Access will use the same name for the field on the form as the table field name. Makes sense, BUT they are different - one refers to the form, the other to the table object.

So for...
Me![lngAddressID]
It may be better to use
Me.lngAddressID


By the way, the name of the field or lable on the form can be changed in the properties under the &quot;Other&quot; tab. Warning. If you change the name of a field, any event associated VBA code will not work. Example, you could change the name of your Combo Box from Combo10 to SelectAddress.

Next,
Do you need the line...
Me![lngAddressID] = Null
?
From what I understand of your code, this value will be reset, and the requery command will definitely reset it.


Lastly,
A great tool for trouble shooting this type of thing is to add
Stop
in the code. Then use F8 to step through your code. The error will become more obvious.

Richard

 

[vree]

Thanks will try.

vree

http://www.groundworkssoftware.com

&quot;For a succesful technology, reality must take precedence over public
relations, for nature will not be fooled.&quot;
R.Feynman

 

[vree]

First I needed to handle how to
tell the user to double click an address field to add
a new address:

Option Compare Database
'Instruction on how to add new data.
Private Sub Combo10_NotInList(NewData As String, Response As Integer)
    MsgBox "Double-click this field to add an entry to the list."
    Response = acDataErrContinue
End Sub

Then I needed the actual code for the combo box,
which form to open, (Control Name left at Combo10
here for example.)

'Code for Combo box.
Private Sub Combo10_DblClick(Cancel As Integer)
On Error GoTo Err_AddressID_DblClick
    Dim lngAddressID As Long
    
    If IsNull(Me![Combo10]) Then
        Me![Combo10].Text = ""
    Else
        lngAddressID = Me![Combo10]
        Me![Combo10] = Null
     End If

    DoCmd.OpenForm "frmMainAddress", , , , , acDialog, "GotoNew"
    Me![Combo10].Requery
    If lngAddressID <> 0 Then Me![Combo10] = lngAddressID
      
Exit_Combo10_DblClick:
    Exit Sub

Err_AddressID_DblClick:
    MsgBox Err.Description
    Resume Exit_Combo10_DblClick
End Sub
[\code]

[COLOR=red]Then I needed code in the On Load event of the form to be opened. (GoToNew).[/color] 

[code]'Coded needed in the On Load Event of any form you want to add new data to.
Private Sub Form_Load()
    If Me.OpenArgs = &quot;GotoNew&quot; And Not IsNull(Me![lngAddressID]) Then
        DoCmd.DoMenuItem acFormBar, 3, 0, , acMenuVer70
    End If
End Sub

victoria

vree

http://www.groundworkssoftware.com

&quot;For a succesful technology, reality must take precedence over public
relations, for nature will not be fooled.&quot;
R.Feynman

 

[bharat11]

I am trying to change the data type of all fields in a table from longinteger to double and the format type to percent. being an absolute newbie I am having no luck with this. I have so many fields in the table that doing it manually is a real pain..
here is the code so far. I get an error on the fld.properties line.

Set dbmydb = CurrentDb
Set tdf = CurrentDb.TableDef.("firmreturnsnet").OpenRecordset
Set dbfirmrtnsnet = dbmydb.OpenRecordset("firmreturnsnet")

Set fld = tdf.Fields
For Each fld In tdf.Fields
fld.Properties.Type = DataType(DB_DOUBLE)

Next fld

dbfirmrtnsnet.Update
dbfirmrtnsnet.Close
dbmydb.Close
End Sub

would appreciate some guidance. I am using access 2000

 

[bharat11]

sorry I posted in the wrong forum. you are free to ignore.