Trouble with Subforms, data input, refresh, and requery.

[joann007]

I WILL BUY YOU A BEER IF YOU CAN HELP ME WITH THIS!!!!!!!

Sorry about the long title-I wanted to make sure I got all the keywords
in there. Here's rundown of my troubles:

I'm a legislative researcher PA and I was asked to build a database
tracking all proposed bills and related documents. Mind you, I had not
ever used Access before three months ago...so be kind.

My database works spectacular until you get to one of my data entry
forms-frmDocuments. This form contains 3 tabs with subforms contained
in them, with other subforms in those, so it gets pretty confusing.
My search form (which works wonderfully, thank you Allen Browne) opens
up frmDocuments. frmDocuments is composed of three tabs:
tabGeneralInfo, tabAnalyst, and tabEvents.
On the main form of tabAnalyst is subform subfAnalystAttatched.
SubfAnalystAttatched (yes, I do know I spelled that wrong) has the
following text boxes: DocID (not enabled), Analyst, Date Assigned, and
Due Date. It also has three check boxes that show whether a fiscal
note, memo, or folder are needed. All of this information is coming
from the Master table and reflect the bill selected by the researcher
on the search form.
SubfAnalystAttatched also has two subforms: SubfDocAddNote and
subfDocNote (I've made sure these are the control names).
SubfDocNote is a continuous subform that lists all the notes associated
with that piece of legislation. SubfDocNote is getting it's data from
the Notes table. The Data Property is set to Link Child Fields:
DocumentID (from Notes table)
Link Master Fields: Documents_DocID (primary autonumber from Master
table)

I would like the researchers to be able to type notes in a text box on
subformDocAddNote, press a control that would save the new record to
the Notes table, and then refresh subfDocNotes to reflect the addition
of that note.

I've been trying to do this for two weeks now, and I'm desperate. I've
tried modules, refresh, requery, append queries, and everything else
under the sun. Unfortunately, I've had no luck. A save record and
then requery statement in SQL works fine for the first note, but when I
try to add a second note associated with the DocID, it overwrites the
first. Oh, what is a girl to do?!

So, if someone out there can help me do this, I will buy them a beer
next time you pass through Harrisburg PA. Please, I'm desperate!

Here's a summary of the info in the db, let me know if you need
anything else.
Tables
Master
-Documents_DocID (autonumber)
-lastname (text)
-DateAssigned (number)
-Date_Due (number)
-fiscal_note (Y/N)
-Memo (Y/N)
-Folder (Y/N)
Notes
-NoteID (autonumber, primary)
-Note (Memo)
-NoteDate (Date/Time)
-DocumentID (Number)

Main Form:
frmDocuments-contains 3 tab controls, nothing else

TabControl:
tabAnalyst (there are two other tabs, but I'm not concerned with
them...they work)

Subform on tabAnalyst:
subfAnalystAttatched

SubfAnalystAttatched:
txtDocuments_DocID (text box, not enabled)
txtlastname (text box)
txtDate_assigned (text box)
txtDate_Due (text box)
chkfiscal_note (check box)
chkMemo (check box)
chkFolder (check box)
***Control sources all set to related fields in Master table***
subfNewDocNote (subform, Linked Child Fields= DocID from Notes
table, Linked Parent Fields= Documents_DocID from Master table)
subfDocNote (subform, Linked Child Fields= DocID from Notes
table, Linked Parent Fields= Documents_DocID from Master table)

subfNewDocNote:
txtNoteDate (text box, control source= NoteDate from Notes table)
txtID (text box, text box, control source= Document ID from Notes
table, not enabled)
txtNoteID( textbox, not enabled, control source= NoteID from Notes
table)
txtNewComment (textbox, control source=Note from Notes table)
*When I make this text box unbound, it doesn't let me type
anything in it*
subfDocNote:
txtNoteDate (control source= NoteDate from Notes table)
txtDocument ID (control source= DocumentID from Notes table)
txtNote (control source = Note from Notes table)
***All are text boxes, visible, but not enabled***
I did try to put subfNewDocNote and subfDocNote all on the same
subform, but then it wouldn't let me type into text box to insert a new
note...go figure.

Please give me some advice, it would be greatly appreciated!

 

[strive4peace]

Hi Joann,

whew! that IS a mouthful! Can you possibly email me your database so I
can put it into perspective?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[joann007]

Thank you so much for agreeing to help me! I'm not in the office right
now, but I will be tomorrow morning at 8:30 am. I'll email you a copy
of the DB then.

THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU

 

[strive4peace]

you're welcome, Joann. I will email you back when I get it and make
comments back to this thread.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[joann007]

I just emailed it a couple of minutes ago, so it should be on his way.

You're awesome!

 

[strive4peace]

Hi Joann,

db on its way back to you. Here are comments on what was done:

This is the key. After you make a change to a Note, save the record.

'~~~~~~~~~~~~~~~~~~~~~
Private Sub txtNewComment_AfterUpdate()
Me.Dirty = False
End Sub
'~~~~~~~~~~~~~~~~~~~~~

You will not need the Refresh button

Turn Vertical Scrollbar on in subfDocNote

Code behind subfDocNote

'~~~~~~~~~~~~~~~~~~~~~
Private Function SortMe()
Me.OrderBy = "NoteDate desc"
Me.OrderByOn = True
On Error Resume Next
If Me.Dirty Then Me.Dirty = False
Me.Requery
End Function
'~~~~~~~~~~~~~~~~~~~~~

In subfDocNote , on form AfterUpdate, Load, AfterDeleteConfirm -->
=SortMe()

For both of the subforms on the Analyst tab, you have LinkMasterFields
set to -->
Documents_Doc ID

Yet this field is not on subfAnalyst
… get to that in a minute…

'~~~~~~~~~~~~~~~~~~~~~
Your Master table needs a PrimaryKey index set on Documents_Doc ID

Before you can do that, you need to remove the duplicate records.

1. Add this field to Master table:
Flag, Yes/No

2. put this code into a general module

'~~~~~~~~~~~~~~~~~~~~~~~
Sub DeleteMasterDuplicates()
'crystal
'strive4peace2006 at yahoo dot com
'11-6-06

'NEEDS REFERENCE TO
'Microsoft DAO Object Library

Dim r As DAO.Recordset

Dim s As String _
, mDocumentID As Long

s = "UPDATE Master SET Flag = False;"
CurrentDb.Execute s

mDocumentID = 0

s = "SELECT * FROM Master ORDER BY [Documents_Doc ID];"

Set r = CurrentDb.OpenRecordset(s, dbOpenDynaset)
r.MoveFirst

Do While Not r.EOF
If r![Documents_Doc ID] <> mDocumentID Then
mDocumentID = r![Documents_Doc ID]
Else
r.Edit
r!Flag = True
r.Update
End If
r.MoveNext
Loop

r.Close
Set r = Nothing

CurrentDb.TableDefs.Refresh

s = "DELETE M.* " _
& " FROM Master AS M " _
& " WHERE M.Flag=True;"
CurrentDb.Execute s

MsgBox "Done deleting duplicates in Master" _
, , "Done"


End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

3. you need to reference a Microsoft DAO Object Library -->
Tools, References

4. compile the code and fix any problems (if you get a yellow line)
From the menu --> Debug, Compile

5. click in the code and press F5 to run it

Now set the PrimaryKey on the Master table autonumber field.

You really should rename
Documents_Doc ID --> DocumentID

The name should be consistent in all tables (IMHO) and should not
contain spaces. Another consideration on the length of the fieldname
compared to the length of the data. if you have short data, choose a
short name. If you have long data, still choose a short name

'~~~~~~~~~~~~~~~~~~~~~

Do not use Captions in table design – users should not open tables
directly and captions mask the true field name

Do not use # in field names – this is the character used to delimit dates

Do not use spaces in field names

Best to just use letters, underscore, and numbers (but NEVER start a
name with a number).

'~~~~~~~~~~~~~~~

Now lets go back to subfAnalyst

Where is Documents_Doc ID? There is no control with that Name

Oh! I see you DO have it… the NAME property needs to change (it is
currently Text49. I like to set the Name and the ControlSource the same

Whatever is the NAME property of the control, that is what you should
use in LinkMasterFields

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~`


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[joann007]

Now that I've had a chance to look at the database you sent me, here
are my comments/questions:

1. The reason that there were duplicates in the Master table was
because some documents had more than one tax type and or analyst. I'm
currently going back to an older version of my db and creating a TaxID
and Analyst ID tables in the same way that I did for the notes. I have
deleted these fields from the Master table. Now the master table has
only one record per a document, with the DocID set as the primary key,
and I've linked the Notes, TaxID, and AnalystID tables to it using
DocID.

Now I'm in the long process of going back through my forms and having
the name and tax fields referencing the correct tables ;-)

2. The notes entry box that you created works correctly, but I still
would like it do one more thing:
After someone enters a note in the text box, and it saved to the Notes
table, I would like it to show up in the text box under SubfDocNote
instaneously (ie, without having to close the form and reopen it
later). Is this possible?

Thanks for everything!

 

[joann007]

Hello...one more question. As I said, I created two separate tables
for Analyst and Tax Type and removed those fields from the master list.
Now I'm having problems on my search form in referencing these fields.
Here's how the code is currently written. How should I amend the
language so that it gets the input from the tables AnalystID and TaxID
for cmbTaxEdited and cmbAnalyst?


Thanks for everything!

Private Sub CmdSearch_Click()


'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.

'***********************************************************************
'Search by tax combo.Gets exact match of field.
If Not IsNull(Me.cmbTaxEdited) Then
strWhere = strWhere & "([Tax] = """ & Me.cmbTaxEdited & """)
AND "
End If

'Search by session combo. Gets exact match of field.
If Not IsNull(Me.CmbSession) Then
strWhere = strWhere & "([Documents_Session] = """ &
Me.CmbSession & """) AND "
End If

'Search by keyword. Searches for similiar words anywhere in the
field.
If Not IsNull(Me.txtFilterKeyword) Then
strWhere = strWhere & "([Keywords] Like ""*" &
Me.txtFilterKeyword & "*"") AND "
End If

'Search by analyst. Gets exact match of field.
If Not IsNull(Me.cmbAnalyst) Then
strWhere = strWhere & "([lastname] = """ & Me.cmbAnalyst & """)
AND "
End If

'Search by bill type. Gets exact match of field.
If Not IsNull(Me.cmbBillType) Then
strWhere = strWhere & "([TypeCode] = """ & Me.cmbBillType &
""") AND "
End If

'Search by bill #. Gets exact match of field.
If Not IsNull(Me.txtBillNumber) Then
strWhere = strWhere & "([Documents_Bill#] = """ &
Me.txtBillNumber & """) AND "
End If

 

[strive4peace]

Hi Joann,

you're welcome

"I would like it to show up in the text box under SubfDocNote
instaneously (ie, without having to close the form and reopen it
later). Is this possible?"

yes, that is what should happen -- that is what happened when I tested
it... I am assuming you mean you want the new record to be diplayed in
subform subfDocNote, not a textbox

did you try it on the database I sent back to you?


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

Hi Joann,

your search form is bound to the Master table -- if you moved the
fields, you need to include that/those tables in the recordset for your
search form

from the design view of the form, turn on the properties window
View, Properties from the menu

click where the rulers intersect in the upper left to select the form

click on the Data tab in the properties window

click in the RowSource property

click on the Builder button (...) to the right

Yes to make a query based on the table

'~~~~~~~~~~~~~~~~

drag * to the grid to put all the fields from Master in the recordset

add another table(s)

put additional fields on the grid

do a File, Close from the builder screen and then, Yes, to update the
changes
'~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[joann007]

Hey Crystal! Just got into work today. I downloaded your DB again and
tried the add notes features one more time to just make sure. No go.
The only way I can get my new note to show up in the txtNote field of
SubformDocNote is to close the form and then reopen it. Then it
reflects the changes. I'm using Access 2002...could this be the
problem?

 

[joann007]

Dear Crystal,
I tried this method, but when I use the drop down boxes to search by
Analyst or Tax, I get a dialogue box that says "No Action" and "No
Criteria". My guess is that the search form isn't actually based on a
query. The code that controls the search is on the search command
button. Let me know what you think....

Hi Joann,

your search form is bound to the Master table -- if you moved the
fields, you need to include that/those tables in the recordset for your
search form

from the design view of the form, turn on the properties window
View, Properties from the menu

click where the rulers intersect in the upper left to select the form

click on the Data tab in the properties window

click in the RowSource property

click on the Builder button (...) to the right

Yes to make a query based on the table

'~~~~~~~~~~~~~~~~

drag * to the grid to put all the fields from Master in the recordset

add another table(s)

put additional fields on the grid

do a File, Close from the builder screen and then, Yes, to update the
changes
'~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hello...one more question. As I said, I created two separate tables
for Analyst and Tax Type and removed those fields from the master list.
Now I'm having problems on my search form in referencing these fields.
Here's how the code is currently written. How should I amend the
language so that it gets the input from the tables AnalystID and TaxID
for cmbTaxEdited and cmbAnalyst?


Thanks for everything!

Private Sub CmdSearch_Click()


'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.

'***********************************************************************
'Search by tax combo.Gets exact match of field.
If Not IsNull(Me.cmbTaxEdited) Then
strWhere = strWhere & "([Tax] = """ & Me.cmbTaxEdited & """)
AND "
End If

'Search by session combo. Gets exact match of field.
If Not IsNull(Me.CmbSession) Then
strWhere = strWhere & "([Documents_Session] = """ &
Me.CmbSession & """) AND "
End If

'Search by keyword. Searches for similiar words anywhere in the
field.
If Not IsNull(Me.txtFilterKeyword) Then
strWhere = strWhere & "([Keywords] Like ""*" &
Me.txtFilterKeyword & "*"") AND "
End If

'Search by analyst. Gets exact match of field.
If Not IsNull(Me.cmbAnalyst) Then
strWhere = strWhere & "([lastname] = """ & Me.cmbAnalyst & """)
AND "
End If

'Search by bill type. Gets exact match of field.
If Not IsNull(Me.cmbBillType) Then
strWhere = strWhere & "([TypeCode] = """ & Me.cmbBillType &
""") AND "
End If

'Search by bill #. Gets exact match of field.
If Not IsNull(Me.txtBillNumber) Then
strWhere = strWhere & "([Documents_Bill#] = """ &
Me.txtBillNumber & """) AND "
End If

 

[strive4peace]

Hi Joann,

send me your db (since I assume you changed it) and specify what to look
at...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Dear Crystal,
I tried this method, but when I use the drop down boxes to search by
Analyst or Tax, I get a dialogue box that says "No Action" and "No
Criteria". My guess is that the search form isn't actually based on a
query. The code that controls the search is on the search command
button. Let me know what you think....

Hi Joann,

your search form is bound to the Master table -- if you moved the
fields, you need to include that/those tables in the recordset for your
search form

from the design view of the form, turn on the properties window
View, Properties from the menu

click where the rulers intersect in the upper left to select the form

click on the Data tab in the properties window

click in the RowSource property

click on the Builder button (...) to the right

Yes to make a query based on the table

'~~~~~~~~~~~~~~~~

drag * to the grid to put all the fields from Master in the recordset

add another table(s)

put additional fields on the grid

do a File, Close from the builder screen and then, Yes, to update the
changes
'~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hello...one more question. As I said, I created two separate tables
for Analyst and Tax Type and removed those fields from the master list.
Now I'm having problems on my search form in referencing these fields.
Here's how the code is currently written. How should I amend the
language so that it gets the input from the tables AnalystID and TaxID
for cmbTaxEdited and cmbAnalyst?


Thanks for everything!

Private Sub CmdSearch_Click()


'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.

'***********************************************************************
'Search by tax combo.Gets exact match of field.
If Not IsNull(Me.cmbTaxEdited) Then
strWhere = strWhere & "([Tax] = """ & Me.cmbTaxEdited & """)
AND "
End If

'Search by session combo. Gets exact match of field.
If Not IsNull(Me.CmbSession) Then
strWhere = strWhere & "([Documents_Session] = """ &
Me.CmbSession & """) AND "
End If

'Search by keyword. Searches for similiar words anywhere in the
field.
If Not IsNull(Me.txtFilterKeyword) Then
strWhere = strWhere & "([Keywords] Like ""*" &
Me.txtFilterKeyword & "*"") AND "
End If

'Search by analyst. Gets exact match of field.
If Not IsNull(Me.cmbAnalyst) Then
strWhere = strWhere & "([lastname] = """ & Me.cmbAnalyst & """)
AND "
End If

'Search by bill type. Gets exact match of field.
If Not IsNull(Me.cmbBillType) Then
strWhere = strWhere & "([TypeCode] = """ & Me.cmbBillType &
""") AND "
End If

'Search by bill #. Gets exact match of field.
If Not IsNull(Me.txtBillNumber) Then
strWhere = strWhere & "([Documents_Bill#] = """ &
Me.txtBillNumber & """) AND "
End If

 

[joann007]

Sure thing. I'll send it now.

Hi Joann,

send me your db (since I assume you changed it) and specify what to look
at...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Dear Crystal,
I tried this method, but when I use the drop down boxes to search by
Analyst or Tax, I get a dialogue box that says "No Action" and "No
Criteria". My guess is that the search form isn't actually based on a
query. The code that controls the search is on the search command
button. Let me know what you think....

Hi Joann,

your search form is bound to the Master table -- if you moved the
fields, you need to include that/those tables in the recordset for your
search form

from the design view of the form, turn on the properties window
View, Properties from the menu

click where the rulers intersect in the upper left to select the form

click on the Data tab in the properties window

click in the RowSource property

click on the Builder button (...) to the right

Yes to make a query based on the table

'~~~~~~~~~~~~~~~~

drag * to the grid to put all the fields from Master in the recordset

add another table(s)

put additional fields on the grid

do a File, Close from the builder screen and then, Yes, to update the
changes
'~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



(e-mail address removed) wrote:
Hello...one more question. As I said, I created two separate tables
for Analyst and Tax Type and removed those fields from the master list.
Now I'm having problems on my search form in referencing these fields.
Here's how the code is currently written. How should I amend the
language so that it gets the input from the tables AnalystID and TaxID
for cmbTaxEdited and cmbAnalyst?


Thanks for everything!

Private Sub CmdSearch_Click()


'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.

'***********************************************************************
'Search by tax combo.Gets exact match of field.
If Not IsNull(Me.cmbTaxEdited) Then
strWhere = strWhere & "([Tax] = """ & Me.cmbTaxEdited & """)
AND "
End If

'Search by session combo. Gets exact match of field.
If Not IsNull(Me.CmbSession) Then
strWhere = strWhere & "([Documents_Session] = """ &
Me.CmbSession & """) AND "
End If

'Search by keyword. Searches for similiar words anywhere in the
field.
If Not IsNull(Me.txtFilterKeyword) Then
strWhere = strWhere & "([Keywords] Like ""*" &
Me.txtFilterKeyword & "*"") AND "
End If

'Search by analyst. Gets exact match of field.
If Not IsNull(Me.cmbAnalyst) Then
strWhere = strWhere & "([lastname] = """ & Me.cmbAnalyst & """)
AND "
End If

'Search by bill type. Gets exact match of field.
If Not IsNull(Me.cmbBillType) Then
strWhere = strWhere & "([TypeCode] = """ & Me.cmbBillType &
""") AND "
End If

'Search by bill #. Gets exact match of field.
If Not IsNull(Me.txtBillNumber) Then
strWhere = strWhere & "([Documents_Bill#] = """ &
Me.txtBillNumber & """) AND "
End If

 

[strive4peace]

Hi Joanne,

to clarify, your Search form actually filters (instead of Finds)
records, so what you want to filter on needs to be in the form
Recordset. Currently, your form is based on the Master table.

To add Tax filtering capability, change the form RecordSource to -->
SELECT Master.*, DocTaxes.TaxID
FROM Master
INNER JOIN DocTaxes ON Master.[Documents_Doc ID]=DocTaxes.DocID;

add TaxID to your form.
Name --> TaxID
ControlSource --> TaxID
BackColor --> black
foreColor --> white
Visible --> No

(I like to set invisible controls to be white on black so they show up
well in the design view)

now, make a combobox:

Name --> FilterDoc_Tax
RowSource -->
SELECT DISTINCT TaxCodes.TaxID
, TaxCodes.TaxCode
, TaxCodes.Descrip
FROM TaxCodes
ORDER BY TaxCodes.TaxCode;

modify the code behind the form in your Search routine -->

'~~~~~~~~~~~~~~~~~
'Search by tax combo.Gets exact match of field.
If Not IsNull(Me.FilterDoc_Tax) Then
strWhere = strWhere _
& " ([TaxID] = " _
& Me.FilterDoc_Tax & ") AND "
End If
'~~~~~~~~~~~~~~~~~

you really should rename
Documents_Doc ID --> DocID in your Master table



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Dear Crystal,
I tried this method, but when I use the drop down boxes to search by
Analyst or Tax, I get a dialogue box that says "No Action" and "No
Criteria". My guess is that the search form isn't actually based on a
query. The code that controls the search is on the search command
button. Let me know what you think....

Hi Joann,

your search form is bound to the Master table -- if you moved the
fields, you need to include that/those tables in the recordset for your
search form

from the design view of the form, turn on the properties window
View, Properties from the menu

click where the rulers intersect in the upper left to select the form

click on the Data tab in the properties window

click in the RowSource property

click on the Builder button (...) to the right

Yes to make a query based on the table

'~~~~~~~~~~~~~~~~

drag * to the grid to put all the fields from Master in the recordset

add another table(s)

put additional fields on the grid

do a File, Close from the builder screen and then, Yes, to update the
changes
'~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hello...one more question. As I said, I created two separate tables
for Analyst and Tax Type and removed those fields from the master list.
Now I'm having problems on my search form in referencing these fields.
Here's how the code is currently written. How should I amend the
language so that it gets the input from the tables AnalystID and TaxID
for cmbTaxEdited and cmbAnalyst?


Thanks for everything!

Private Sub CmdSearch_Click()


'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.

'***********************************************************************
'Search by tax combo.Gets exact match of field.
If Not IsNull(Me.cmbTaxEdited) Then
strWhere = strWhere & "([Tax] = """ & Me.cmbTaxEdited & """)
AND "
End If

'Search by session combo. Gets exact match of field.
If Not IsNull(Me.CmbSession) Then
strWhere = strWhere & "([Documents_Session] = """ &
Me.CmbSession & """) AND "
End If

'Search by keyword. Searches for similiar words anywhere in the
field.
If Not IsNull(Me.txtFilterKeyword) Then
strWhere = strWhere & "([Keywords] Like ""*" &
Me.txtFilterKeyword & "*"") AND "
End If

'Search by analyst. Gets exact match of field.
If Not IsNull(Me.cmbAnalyst) Then
strWhere = strWhere & "([lastname] = """ & Me.cmbAnalyst & """)
AND "
End If

'Search by bill type. Gets exact match of field.
If Not IsNull(Me.cmbBillType) Then
strWhere = strWhere & "([TypeCode] = """ & Me.cmbBillType &
""") AND "
End If

'Search by bill #. Gets exact match of field.
If Not IsNull(Me.txtBillNumber) Then
strWhere = strWhere & "([Documents_Bill#] = """ &
Me.txtBillNumber & """) AND "
End If

 

[joann007]

Crystal,
It've been busy with the holidays, but I wanted to write and thank
you so much for helping me. My database is working wonderful now and
on it's well on it's way to completion...I truly could not have done it
without you!

P.S.-I'll be thinking about you as I watch Nova tonight!

Hi Joann,

your search form is bound to the Master table -- if you moved the
fields, you need to include that/those tables in the recordset for your
search form

from the design view of the form, turn on the properties window
View, Properties from the menu

click where the rulers intersect in the upper left to select the form

click on the Data tab in the properties window

click in the RowSource property

click on the Builder button (...) to the right

Yes to make a query based on the table

'~~~~~~~~~~~~~~~~

drag * to the grid to put all the fields from Master in the recordset

add another table(s)

put additional fields on the grid

do a File, Close from the builder screen and then, Yes, to update the
changes
'~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hello...one more question. As I said, I created two separate tables
for Analyst and Tax Type and removed those fields from the master list.
Now I'm having problems on my search form in referencing these fields.
Here's how the code is currently written. How should I amend the
language so that it gets the input from the tables AnalystID and TaxID
for cmbTaxEdited and cmbAnalyst?

Thanks for everything!

Private Sub CmdSearch_Click()

'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.

'***********************************************************************
'Search by tax combo.Gets exact match of field.
If Not IsNull(Me.cmbTaxEdited) Then
strWhere = strWhere & "([Tax] = """ & Me.cmbTaxEdited & """)
AND "
End If

'Search by session combo. Gets exact match of field.
If Not IsNull(Me.CmbSession) Then
strWhere = strWhere & "([Documents_Session] = """ &
Me.CmbSession & """) AND "
End If

'Search by keyword. Searches for similiar words anywhere in the
field.
If Not IsNull(Me.txtFilterKeyword) Then
strWhere = strWhere & "([Keywords] Like ""*" &
Me.txtFilterKeyword & "*"") AND "
End If

'Search by analyst. Gets exact match of field.
If Not IsNull(Me.cmbAnalyst) Then
strWhere = strWhere & "([lastname] = """ & Me.cmbAnalyst & """)
AND "
End If

'Search by bill type. Gets exact match of field.
If Not IsNull(Me.cmbBillType) Then
strWhere = strWhere & "([TypeCode] = """ & Me.cmbBillType &
""") AND "
End If

'Search by bill #. Gets exact match of field.
If Not IsNull(Me.txtBillNumber) Then
strWhere = strWhere & "([Documents_Bill#] = """ &
Me.txtBillNumber & """) AND "
End If- Hide quoted text -- Show quoted text -

 

[strive4peace]

Hi Joann,

you are welcome happy to help

Thanks for writing back... glad you are having success with your project.

Nova... thanks! Nice to know I am on someone's mind. If I had tv, I
would probably watch not only the educational stuff, but also the sappy
romance movies and stay up all night ... I do enjoy them so much, but I
am weak and get hooked so, like potato chips, I just don't buy them

Enjoy the Christmas holidays -- my best to you and your man!

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*