Some code help....

  • Thread starter Thread starter cathywoodford
  • Start date Start date
C

cathywoodford

Hi. I have an Access Database with a bunch of different tables. Each
table has a field called "Tag Number" and also each table has its own
specific form. I have a search form that lets the users search for a
"Tag Number". The part I need help with is how do I tell the search
button to open the right form depending on the "Tag Number" specified
in the search text box? Example: Tag Number 123456 comes from
tblNumbers which should open all information for that particular tag
number in frmNumbers, Tag Number ABCDEF comes from tblLetters which
should open all informtion for that particular tag number in
frmLetters. I hope this makes sense and someone can help. I know the
code would be on the "Search" button and I think it may have to be a
Select Case or something like that.
 
Thanks for the reply.

But the user can search on any particular tag number (22 different
tables with different tag numbers).

So, I was thinking along the lines of

if [tagnumber typed in search box] is in tblNumbers then
docmd.openform "frmNumbers",,,[TagNumber]=Me![TagNumber]
else if [tagnumber typed in search box] is in tblLetters then
docmd.openform "frmLetters",,,[TagNumber]=Me![TagNumber]
else if [tagnumber typed in search box] is in tblAbbreviations then
docmd.openform "frmAbbrev",,,[TagNubmer]=Me![TagNumber]
End if

I know this isn't the right syntax but it should work something like
that. I have it working as you said below where I just have 1 table
and 1 form for the values to open in. The delima is that I have to
open a form based on where the tag number the user searched on comes
from.
DoCmd.OpenForm "frmNumbers", , , [TagNumber]=Me![TagNumber]

Keven

Hi. I have an Access Database with a bunch of different tables. Each
table has a field called "Tag Number" and also each table has its own
specific form. I have a search form that lets the users search for a
"Tag Number". The part I need help with is how do I tell the search
button to open the right form depending on the "Tag Number" specified
in the search text box? Example: Tag Number 123456 comes from
tblNumbers which should open all information for that particular tag
number in frmNumbers, Tag Number ABCDEF comes from tblLetters which
should open all informtion for that particular tag number in
frmLetters. I hope this makes sense and someone can help. I know the
code would be on the "Search" button and I think it may have to be a
Select Case or something like that.
Click to expand...
Click to expand...
 
Possibly needs some ""s in there...
DoCmd.OpenForm "frmNumbers", , , "[Tag Number]=" & Me.SearchBox
DoCmd.OpenForm "frmLetters", , , "[Tag Number]='" & Me.SearchBox & "'"

--
Steve Schapel, Microsoft Access MVP
DoCmd.OpenForm "frmNumbers", , , [TagNumber]=Me![TagNumber]

Keven

Hi. I have an Access Database with a bunch of different tables. Each
table has a field called "Tag Number" and also each table has its own
specific form. I have a search form that lets the users search for a
"Tag Number". The part I need help with is how do I tell the search
button to open the right form depending on the "Tag Number" specified
in the search text box? Example: Tag Number 123456 comes from
tblNumbers which should open all information for that particular tag
number in frmNumbers, Tag Number ABCDEF comes from tblLetters which
should open all informtion for that particular tag number in
frmLetters. I hope this makes sense and someone can help. I know the
code would be on the "Search" button and I think it may have to be a
Select Case or something like that.
Click to expand...
Click to expand...
 
Thanks. Is this gonna work and know to pick up what table the tag
number comes from or do I need the IF statement. Sorry to be such a
pain but I'm new at this coding stuff.


Steve said:
Possibly needs some ""s in there...
DoCmd.OpenForm "frmNumbers", , , "[Tag Number]=" & Me.SearchBox
DoCmd.OpenForm "frmLetters", , , "[Tag Number]='" & Me.SearchBox & "'"

--
Steve Schapel, Microsoft Access MVP
DoCmd.OpenForm "frmNumbers", , , [TagNumber]=Me![TagNumber]

Keven

Hi. I have an Access Database with a bunch of different tables. Each
table has a field called "Tag Number" and also each table has its own
specific form. I have a search form that lets the users search for a
"Tag Number". The part I need help with is how do I tell the search
button to open the right form depending on the "Tag Number" specified
in the search text box? Example: Tag Number 123456 comes from
tblNumbers which should open all information for that particular tag
number in frmNumbers, Tag Number ABCDEF comes from tblLetters which
should open all informtion for that particular tag number in
frmLetters. I hope this makes sense and someone can help. I know the
code would be on the "Search" button and I think it may have to be a
Select Case or something like that.
Click to expand...
Click to expand...
Click to expand...
 
Cathy,

In my experience, this is a very unusual requirement. It probably
indicates a flaw in the design of your tables. What is the nature of
the differences between these 22 tables? Possibly a change in your
database structure would be advisable.

I assume there are other tables in the database? Is there anything that
distinguishes these 22 tables from the others, for example in the table
names?

And am I correct in my understanding that in some of the tables, the Tag
Number field is a Number data type, and in others it is a Text data type?

And am I correct in my understanding that the same Tag Number is not
used in more than one table? If so, how do you enforce this?

So, every time, you will need your code to look through all the tables
until it finds the requested Tag Number value. The specifics of how to
do this will vary according to your answers to some of the above.
 
Thank you so much for taking the time to reply.

This is a very unusual case I know. The difference in the 22 tables
are the fields for the tag numbers contained in the table. For
example, tblNumbers may have Title, ID Number, Purchase Order Number,
Weight and tblLetters may have Service, Operating Temperature,
Pressure, Dial Size etc.
There are only these 22 tables in the database and each one is named
tblCode63, tblCode9 etc.

You are correct, the same tag number is not used in more than one
table. This information is being imported into the table and is not
going to change. This database is for reference purposes and updating
any information related to tags in these tables. No one will be
adding/changing tag numbers once the tables are complete and imported.

Correct - the code should find which table the tag is in and then open
the form accordingly. My forms are named as my tables (ie: tblCode6
and frmCode6)

All tag numbers are text.

The main purpose of the different 22 tables is that there are different
fields associated with different tags. In one table there could be 30
tags all with the same fields and in another there could be 100 tags
with the same fields, which is why I created 22 different templates.
Also, the template of the printed form had to be arranged in a certain
order.

Hopefully this explains it a little better.

Thanks again and I hope you can help.
 
Cathy,

Thanks for your further explanation. Well, it's not as difficult as it
could have been :-)

Here is some (untested!) "air code" that should get you going in the
right direction.

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim FormName As String
Dim strCrit As String
strCrit = "[Tag Number]='" & Me.SearchBox & "'"
' (replace SearchBox with actual name of textbox where tag number
requested)
Set dbs = DBEngine(0)(0)
For Each tdf In dbs.TableDefs
Set rst = dbs.OpenRecordset("SELECT [Tag Number] FROM " & tdf.Name
& " WHERE " & strCrit
If rst.RecordCount > 0 Then
FormName = "frm" & Mid(tdf.Name, 4)
Exit For
End If
rst.Close
Next tdf
DoCmd.OpenForm FormName, , , strCrit
Set rst = Nothing
Set dbs = Nothing
 
Thank you so much for your time at this!!! If this works you will be
my lifesaver :-)

I've put the code in the code window but it doesn't like:
Set rst = dbs.OpenRecordset("SELECT [Tag Number] FROM " & tdf.Name
& " WHERE " & strCrit

The error is Compile Error: Expected: Line number or label or
statement or end of statement. Any ideas?

I'm keeping my fingers crossed that this works so I don't have to
change my whole database around.

Thanks again.
Cathy

Steve said:
Cathy,

Thanks for your further explanation. Well, it's not as difficult as it
could have been :-)

Here is some (untested!) "air code" that should get you going in the
right direction.

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim FormName As String
Dim strCrit As String
strCrit = "[Tag Number]='" & Me.SearchBox & "'"
' (replace SearchBox with actual name of textbox where tag number
requested)
Set dbs = DBEngine(0)(0)
For Each tdf In dbs.TableDefs
Set rst = dbs.OpenRecordset("SELECT [Tag Number] FROM " & tdf.Name
& " WHERE " & strCrit
If rst.RecordCount > 0 Then
FormName = "frm" & Mid(tdf.Name, 4)
Exit For
End If
rst.Close
Next tdf
DoCmd.OpenForm FormName, , , strCrit
Set rst = Nothing
Set dbs = Nothing

--
Steve Schapel, Microsoft Access MVP

Thank you so much for taking the time to reply.

This is a very unusual case I know. The difference in the 22 tables
are the fields for the tag numbers contained in the table. For
example, tblNumbers may have Title, ID Number, Purchase Order Number,
Weight and tblLetters may have Service, Operating Temperature,
Pressure, Dial Size etc.
There are only these 22 tables in the database and each one is named
tblCode63, tblCode9 etc.

You are correct, the same tag number is not used in more than one
table. This information is being imported into the table and is not
going to change. This database is for reference purposes and updating
any information related to tags in these tables. No one will be
adding/changing tag numbers once the tables are complete and imported.

Correct - the code should find which table the tag is in and then open
the form accordingly. My forms are named as my tables (ie: tblCode6
and frmCode6)

All tag numbers are text.

The main purpose of the different 22 tables is that there are different
fields associated with different tags. In one table there could be 30
tags all with the same fields and in another there could be 100 tags
with the same fields, which is why I created 22 different templates.
Also, the template of the printed form had to be arranged in a certain
order.
Click to expand...
Click to expand...
 
Cathy,

Told you it was untested! :-) I forgot to put the ) at the end. Try
this...
Set rst = dbs.OpenRecordset("SELECT [Tag Number] FROM " & tdf.Name & "
WHERE " & strCrit)
 
Hi Steve. I'm left my office for the day but I did give it a try and
it was like it wasn't picking up the table names. I wonder if I should
have said:
Set rst = dbs.OpenRecordset("SELECT * FROM " & tdf.Name & " WHERE " &
strCrit)
with star * instead of TAG_NUMBER because we already pass the tag
number into the strCrit at the end of the statement. What do you
think? I will be working on this again tomorrow if you're around :-)

Thanks again.

Cathy

Steve said:
Cathy,

Told you it was untested! :-) I forgot to put the ) at the end. Try
this...
Set rst = dbs.OpenRecordset("SELECT [Tag Number] FROM " & tdf.Name & "
WHERE " & strCrit)

--
Steve Schapel, Microsoft Access MVP

Thank you so much for your time at this!!! If this works you will be
my lifesaver :-)

I've put the code in the code window but it doesn't like:
Set rst = dbs.OpenRecordset("SELECT [Tag Number] FROM " & tdf.Name
& " WHERE " & strCrit

The error is Compile Error: Expected: Line number or label or
statement or end of statement. Any ideas?
Click to expand...
Click to expand...
 
Cathy,

No, that won't be the problem. Unless you've spelt the name of the
field wrong... what's with the underscore now in TAG_NUMBER???

Can you copy/paste the entire code you have now? And also tell me
what's happening... error message, or just no form opens, or what? Thanks.
 
Hi Steve. First off to answer your question about the underscore -
that's the field name but I just had forgot to use it in the code
earlier :-)

Just at home thinking about it and was wondering about the whole
recordset bit. Would my tables be automatically set up like that or
would I have to do it? This is just a basic database that was created
using wizards and stuff like that. As you are probably already aware -
I'm just a beginner but am very eager to learn (which I'm sure is what
I'm going to have to do to finish this).

Also, if I can remember correctly when I debugged the code the table
name came up with something like "mysys...."?

Anyway, I'll get back to you tomorrow for sure with the exact
information. I'm thinking maybe it is better for me just to combine
all the tables into one (if possible) and go from there. What do you
think?

Thanks,
Cathy
 
Cathy,

As for the underscore in the field name, that's cool. As long as this
is the exact spelling of the field name in *all* of the affected tables,
and as long as we do the same in the code, we're fine with that. Always
best, though, when asking for help on the newsgroups, to provide the
correct information from the beginning, because attention to these
little details is all the helpers have to go on, so it gets confusing
when names get changed mid-stream.

Having said that, I forgot about the hidden system tables. Suggested
amendment to code below...

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim FormName As String
Dim strCrit As String
strCrit = "TAG_NUMBER='" & Me.SearchBox & "'"
' (replace SearchBox with actual name of textbox where tag number
requested)
Set dbs = DBEngine(0)(0)
For Each tdf In dbs.TableDefs
If tdf.Name Like "tbl*" Then
Set rst = dbs.OpenRecordset("SELECT TAG_NUMBER FROM " & tdf.Name
& " WHERE " & strCrit)
If rst.RecordCount > 0 Then
FormName = "frm" & Mid(tdf.Name, 4)
Exit For
End If
rst.Close
End If
Next tdf
DoCmd.OpenForm FormName, , , strCrit
Set rst = Nothing
Set dbs = Nothing

As regards your table structure, from what you told me this is a valid
sub-typing type of design, and if the fields used by the 22 different
groupings are indeed significantly different from each other, then it is
probably correct to have 22 separate tables. However, this type of
design would usually have a "hub" table, which contains the Tag_Number
field for *all* records from *all* sub-typed tables, and also any other
fields which are common to all existing 22 tables (it would be unusual
if there was absolutely none). And this hub table would also have an
additional field (Category or some such) which identifies which of the
22 groupings each record is associated with. If you did that, you would
find this type of data searching, and querying, and opening the
appropriate forms or reports, etc, would be a lot simpler, because most
of the grouping and selecting etc is based on the data in the one
central table.
 
Good morning Steve. Just tried the new code as follows
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim FormName As String
Dim strCrit As String
strCrit = "TAG_NUMBER='" & Me.Tag_Number & "'"
' (replace SearchBox with actual name of textbox where tag number
requested)
Set dbs = DBEngine(0)(0)
For Each tdf In dbs.TableDefs
If tdf.Name Like "tbl*" Then
Set rst = dbs.OpenRecordset("SELECT TAG_NUMBER FROM " &
tdf.Name & " WHERE " & strCrit)
If rst.RecordCount > 0 Then
FormName = "frm" & Mid(tdf.Name, 4)
Exit For
End If
rst.Close
End If
Next tdf
DoCmd.OpenForm FormName, , , strCrit
Set rst = Nothing
Set dbs = Nothing

And I'm getting an error on Set rst = dbs.OpenRecordset("SELECT
TAG_NUMBER FROM " & tdf.Name & " WHERE " & strCrit). The error is: Too
few parameters. Expected 1.

Just a quick thought on what you said about the "hub table". How about
I created a table with 2 fields: TAG_NUMBER which would contain all the
tag numbers from each table and FORM_NAME which would contain the name
of the form that should open. Would that be easier? I would still
have my individual forms referencing the coinsiding table with the
filter set to [Tag_Number] like '41-pit-2055'.

Cathy :-)
 
Steve....got that problem figured out but now it's not picking up the
form name. Error: The action or method requires a formname argument
on the Docmd.openform line.

Cathy
Good morning Steve. Just tried the new code as follows
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim FormName As String
Dim strCrit As String
strCrit = "TAG_NUMBER='" & Me.Tag_Number & "'"
' (replace SearchBox with actual name of textbox where tag number
requested)
Set dbs = DBEngine(0)(0)
For Each tdf In dbs.TableDefs
If tdf.Name Like "tbl*" Then
Set rst = dbs.OpenRecordset("SELECT TAG_NUMBER FROM " &
tdf.Name & " WHERE " & strCrit)
If rst.RecordCount > 0 Then
FormName = "frm" & Mid(tdf.Name, 4)
Exit For
End If
rst.Close
End If
Next tdf
DoCmd.OpenForm FormName, , , strCrit
Set rst = Nothing
Set dbs = Nothing

And I'm getting an error on Set rst = dbs.OpenRecordset("SELECT
TAG_NUMBER FROM " & tdf.Name & " WHERE " & strCrit). The error is: Too
few parameters. Expected 1.

Just a quick thought on what you said about the "hub table". How about
I created a table with 2 fields: TAG_NUMBER which would contain all the
tag numbers from each table and FORM_NAME which would contain the name
of the form that should open. Would that be easier? I would still
have my individual forms referencing the coinsiding table with the
filter set to [Tag_Number] like '41-pit-2055'.

Cathy :-)


Steve said:
Cathy,

As for the underscore in the field name, that's cool. As long as this
is the exact spelling of the field name in *all* of the affected tables,
and as long as we do the same in the code, we're fine with that. Always
best, though, when asking for help on the newsgroups, to provide the
correct information from the beginning, because attention to these
little details is all the helpers have to go on, so it gets confusing
when names get changed mid-stream.

Having said that, I forgot about the hidden system tables. Suggested
amendment to code below...

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim FormName As String
Dim strCrit As String
strCrit = "TAG_NUMBER='" & Me.SearchBox & "'"
' (replace SearchBox with actual name of textbox where tag number
requested)
Set dbs = DBEngine(0)(0)
For Each tdf In dbs.TableDefs
If tdf.Name Like "tbl*" Then
Set rst = dbs.OpenRecordset("SELECT TAG_NUMBER FROM " & tdf.Name
& " WHERE " & strCrit)
If rst.RecordCount > 0 Then
FormName = "frm" & Mid(tdf.Name, 4)
Exit For
End If
rst.Close
End If
Next tdf
DoCmd.OpenForm FormName, , , strCrit
Set rst = Nothing
Set dbs = Nothing

As regards your table structure, from what you told me this is a valid
sub-typing type of design, and if the fields used by the 22 different
groupings are indeed significantly different from each other, then it is
probably correct to have 22 separate tables. However, this type of
design would usually have a "hub" table, which contains the Tag_Number
field for *all* records from *all* sub-typed tables, and also any other
fields which are common to all existing 22 tables (it would be unusual
if there was absolutely none). And this hub table would also have an
additional field (Category or some such) which identifies which of the
22 groupings each record is associated with. If you did that, you would
find this type of data searching, and querying, and opening the
appropriate forms or reports, etc, would be a lot simpler, because most
of the grouping and selecting etc is based on the data in the one
central table.
Click to expand...
Click to expand...
 
Cathy,

How did you "get that problem figured out"?

The code is supposed to find the table with the requested tag_number,
and then assign the FormName based on the table name. If there is no
FormName, then it means it did not find the tag_number in any of the tables.
 
Hi Steve. No, it picks up the first table and recognizes that there is
no tag number (recordcount = 0) but it goes to open the form instead of
jumping to the next table.
 
Cathy,

Did resolving the "too few parameters" error involve any change to the code?

Can you tell me what makes you think the code is only checking the first
table?

Would it be ok for you to email me the database so I can have a look at it?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Import File Information 0
relations problem 1
Cannot set opening form tag 3
Access 2003 - Help with Make-Table and Append queries 3
populate tag attribute 2
Customize Cross Reference 0
Duplicate values 4
searching for terms? 6

Back
Top