Linking Append Query To A Command Button

[boblarson]

Okay, I think I have it.

1. Go into the query "qryEquipment" and make sure the field is there (I didn't see it in the version you posted about 2 hours ago). But you will have to

2. Change the field to be: PrecisionSheet:Nz([Precision Sheet], "")

3. Change the text box name on the results to txtPrecisionSheet and bind it to PrecisionSheet.

4. Make sure that since you changed the field to Memo that you also removed the # signs from in front and back of the data.

In the sample db you posted you had two links in there but they were not associated with much of anything - no month, year, etc. So make sure you have data to test.

 

[MCatz]

That did the trick! I forgot to go back and create that field in the query for that form.

Now, if I can just get the fields to display text instead of numbers for Area, Further Problems, Month and Year Complete.

I remember you said this quite a few posts ago as a remedy for this problem:

...So you can either, in the subform's recordsource, use a query for its recordsource which includes the other tables and uses their descriptions instead of their ID's...

How would I go about writing said query?

I still haven't quite figured out the proper syntax of code (i.e. what is doing what). Is there anything that explains in layman's terms what this vast quantity of operators does?

 

[boblarson]

You need to link the other tables (the ones with the values to lookup) to the qryEquipment query and link the appropriate field to the ID field of the lookup and then you can place the text description field from the lookup table into the query in place of the one you are using currently.

 

[MCatz]

Hmmm...Must be doing something wrong here.

I'll describe my steps and maybe you can help me see where I'm going wrong:

1) Open qryEquipment in Design View
2) Right Click > Show All Tables
3) Link all fields from EquipmentID_Table (Area, Year Complete, Month Complete, and Further Problems) to each of their corresponding IDs in the other 4 tables (AreaID, MonthID, ProblemID, YearID).
4) Delete Area, Year Complete, Month Complete, and Further Problems columns from query that are pulling from EquipmentID_Table
5) Pull fields from each individual table (Area, Years, Months, and Further Problems) and drag them into the query.

A couple of questions I had along the way...

1) Do each of these fields (Area, Years, Months, Problems) need to be stored as numbers or text? I have them stored as numbers so that they'd link to the ID field of their respective tables, but I'm not really sure if that's right....

2) Do I need to redo each of the subforms to reflect the changes I make with the query?

 

[boblarson]

Re:

#1. Yes, you store the fields (Area, Years, Months, Problems) as numbers. In fact, you wouldn't be so confused if you had named them correctly in Equipment_IDTable. They should be
AreaID
YearID
MonthID
ProblemID

just like in the tables where they are looked up from. You are storing the ID's not the descriptions. That is how it should be.

#2 - You will have to change the control source for any which don't share the same name. So, since you have Area you won't need to do that one but you will have to change the control source for Month Complete to Month and Year Complete to Years and Further Problems to Problems.

 

[MCatz]

Alright...I got the subform to display the text as it is written on the "Search Form", but when I try to search by Month, Year, Area, or Problems, it prompts for an entry instead of using the information I select/type in.

Also, when I try to change the ControlSource on Area, Month, Year, and Problems on the "Record Form", it just displays #NAME?.

I know I'm just missing something simple, but I can't figure out what it is.

 

[MCatz]

Bump it on up!

 

[boblarson]

Sorry, I was pretty busy yesterday. I am uploading the changed search with the changed BuildFilter. What was needed was the field names in the search changed, the text of the combo boxes using .Column(1) referenced (and quotes entered in the search parts). And changing the bound column of the listbox to 2 from 1.

See if you can see all that I did.

 

[MCatz]

I won't say I completely understand what you did there (because I'd be lying), but I can see you declared strBuildFilter as String (not sure what that does) and then changed the "checks" for the drop downs.

What exactly does "Chr(34)" do?

Also, one other thing I forgot to ask, how could I include a search option for Precision State or not? I'd like to have an option group (Yes/No or a simple checkbox) on that search form that filters by that option as well.

How would I go about doing that?

 

[boblarson]

1. I used strBuildFilter so I could get the value and then display it in the debug window. If I were to call it like

Debug.Print BuildFilter

then it would run again which is not necessary. So, having the string variable allows it to run once but be used in both the debug and the regular place.

2. CHR(34) is a double quote and is needed when we are selecting TEXT instead of ID numbers. And because of the way the data is in the search query, we need to search on the text and not the ID.

3. For PrecisionState you could just use a yes/no checkbox and then use something like this as an added piece of BuildFilter:

If Me.PrecisionState Then
   varWhere = varWhere & " [PrecisionState] = True "
End If

 

[MCatz]

3. For PrecisionState you could just use a yes/no checkbox and then use something like this as an added piece of BuildFilter:

If Me.PrecisionState Then
   varWhere = varWhere & " [PrecisionState] = True "
End If

What if I were to use an option group instead?

 

[boblarson]

What if I were to use an option group instead?

If you set the option group values to be -1 for yes and 0 for no then you can use:

If Me.YourOptionGroupNameHere = -1 Then
   varWhere = varWhere & " [PrecisionState] = True "
End If

 

[MCatz]

Hmm...

When I select "Yes", it returns all the ones checked Yes, but when I check "No", it returns everything.

EDIT:

Well...fooling around myself actually worked this time. That seemed to do the trick.

If Me.optPrecision = -1 Then
     varWhere = varWhere & " [Precision State] = True " [B][COLOR=red]& " AND "[/COLOR][/B]
     [B][COLOR=red]Else[/COLOR][/B]
[B][COLOR=red]    varWhere = varWhere & " [Precision State] = False " & " AND "[/COLOR][/B]
     End If

 

[MCatz]

One last question on the Search Form and I'm completely done with it.

The subform doesn't seem to update with new records. I added a couple more records via the "Record Form", but they don't seem to show up in the results of the "Search Form."

Is this another case of making the subform requery again or something along those lines?

 

[boblarson]

They wouldn't show up unless you click the Search button again.

Also, I noticed that your form is adding a blank record (except for a couple of fields which had default values. You might want to take those defaults out (AreaID, YearID, ProblemID, and MonthID in the EquipmentID_Table table).

 

[MCatz]

They wouldn't show up unless you click the Search button again.

I'm not sure what you mean here.

Try it on my most up-to-date version. Enter a record on the "Record Form" and then attempt to search for that same form on the "Search Form". It doesn't "find" it for some reason. The record is appended to the EquipmentID_Table, but it doesn't seem like that's where it searches.

Also, I noticed that your form is adding a blank record (except for a couple of fields which had default values. You might want to take those defaults out (AreaID, YearID, ProblemID, and MonthID in the EquipmentID_Table table).

I didn't see any default values set for these fields.

Access kept saving records when I'd exit out whether I'd entered information or not, so I put a code in the Before Update event to make a message pop up asking if you wanted to save.

 

[boblarson]

First up:

It should look like:

 

[boblarson]

One more thing -

Your option group is messed up. Your YES is set to be 0 and your NO is set to be -1. You need to reverse that.

 

[boblarson]

Okay, found something else. Your query - qryEquipment should look like this (with the outer joins):

 

[MCatz]

First up:

It should look like:

Ahhh...didn't think to look at the table level.

One more thing -

Your option group is messed up. Your YES is set to be 0 and your NO is set to be -1. You need to reverse that.

That was the only way (that I know of) to solve my problem of the option group automatically defaulting to "No" when I entered something in another field. When it defaults to "No", the "Further Problems" combobox won't appear unless you click in the box or click "Yes" then "No" again (which won't be obviously apparent to the user). Do you know of any other way I could fix that problem?

Okay, found something else. Your query - qryEquipment should look like this (with the outer joins):

Awesome! That solved that issue.