adding table to query (1 Viewer)

[jrjr]

My form is based on a select query. There are 2 combos that filter the data displayed in the form by status and property. All of the data is contained in the same table and the query is looking at just the one table. Clicking a button opens a listbox that contains all of the filtered data and clicking a record in the listbox populates the form with that record and closes the listbox. It works good like this.

When I try to add one field (labor) from a second table to the query with that same field added to my form, as long as there is a value in the labor field in the table then it works. If there is no value for labor then the form returns no records. There are records there but they do not have labor assigned yet. I set a default value for the field on my form - (not assigned yet). The listbox displays the records even though there is nothing in the labor field, but clicking a record in the listbox returns an error of 'no current record'.

Actually I dont even have to add the labor field to the form, just having it in the query mucks up the form. I must need an isnotnull statement in the criteria or something.......


Any ideas what I may be doing wrong?

Thanks!

 

[Uncle Gizmo]

>>>>>> There are records there but they do not have labor assigned yet. <<<<

what I have done in situations like this is actually add one record, call it something like "No Labour assigned". I usually endeavor to make it the first record, record number one, then in the corresponding table, where this data links to give the field on the form that collects this data, set the combo box to the default value of one. Now whenever you enter new data in your main table, it will automatically enter the default labor condition of "No labor assigned"

 

[jrjr]

This may not work for me. What I have going on is this.... we have a full maintenance program that covers all aspects of the maintenance business. Included with the package was a separate little generic work order program that was to be installed on users machines. I didnt like it at all, too basic and difficult to get installed. So I wrote one in access. Now we have the main DB on the server, a db that goes on certain machines that is the full blown program (front end), and my work order program that resides on the server and is called over the network by a larger number of people. It has a few linked tables with the main db.

When a work order is placed with my program, it is then reviewed by a person with the full program and this is where labor is assigned, or not. What I am looking at is the records when they are in the 'Requested' state before the order is issued and labor assigned or the ones that the supervisor did not assign labor for whatever reason. Labor will never be assigned with my program, only the full program. I would have to modify the full program to get it to do this I think but it's an mde... If you follow me.....

My labor field on the form is text not a combo. The combos are status and property for filtering with those values. I just was trying to show anyone interested whom the labor was assigned to if they were reviewing 'open' work orders. I can eliminate the field if needed, its really up to me! Ahhhhhh... the power! LOL

 

[jrjr]

Any more ideas on this from anyone?

 

[jrjr]

I just discovered something. I do not have to add the field to the query, just bringing the table in with the relationship throws the error when I try to populate the form from the listbox. "No current Record" even though there is one, but nothing in the other table for the corresponding key.

Here is where it hangs:


Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[WOnumber] = " & Str(Me![Listbox1])
Me.Bookmark = rs.Bookmark
[Forms]![FrmFilter]![FrmFilterCombo].SetFocus

Listbox1.Visible = False
CloseListBtn.Visible = False
OpenListBtn.Visible = True
ClickItemLabel.Visible = False

 

[jrjr]

Will this ever work?

 

[jrjr]

Ok, I guess I will take no answer to mean that this will not work and move on to other things