"Or" in a update query criteria

[Design by Sue]

Is there such a thing as code for "or" in the criteria field of an update query? I can use the same query to update a table based on which of 5 forms happens to be opened at the time. I would like the criteria to read something like:

WHERE ((([TBL 1].[ID]) Like [Forms]![FRM 1]![ID] Or [TBL 1].[ID]) Like [Forms]![FRM 2]![ID] Or [TBL 1].[ID]) Like [Forms]![FRM 3]![ID] Or [TBL 1].[ID]) Like [Forms]![FRM 4]![ID] Or [TBL 1].[ID]) Like [Forms]![FRM s]![ID]));

So it only uses whichever of the forms are open at the time. The only way I could see to do this is create a different query for each of the forms but wondering if I can streamline this.

Thanks
Sue

 

[Design by Sue]

That might be misleading - only one of the forms will be open at any time - so it just needs to look at the one form open and use the ID number from that form.

Sue

 

[Mihail]

In a regular module:

1) Create a PUBLIC variable

Public varID As Long 'I assume that the ID is of type "Long"

2) Create a PUBLIC function

Public Function GetVarID() As Long
  GetVarID = varID
End Function

In each Form manage to fill the varID with the value

Private Sub Form_Current()
  varID = Me.ControlName
End Sub

3) Open the query in Design View and, in the Criteria row, type:

GetVarID()

Should work.

On the other hand, I'm very sure that your DB is far away from a NORMALIZED one.

 

[Design by Sue]

Thank you - but why do you say it is "far from" normalized based on my question??

 

[Mihail]

Because I can't see any reason to have 5 (FIVE) forms with the exact same structure.
Post a pic from the Relationships window and I'll be able to say more.

 

[WayneRyan]

Sue,

You can use the QueryDef Object:

You're essentially just "bolting on" the Where clause for THAT particular form.

Dim qdf as DAO.QueryDef

set qdf = dbs.QueryDefs("YourUniversalQuery")
qdf.sql = Mid(1, Instr(1, qdf.sql, "Where ") - 1) & " Where [TBL 1].ID Like [Forms]![[SIZE="3"][COLOR="black"][U][B]YourForm[/B][/U][/COLOR][/SIZE]]![ID]"
qdf.close

hth,
Wayne

 

[Design by Sue]

Mihail - I am trying your method but I keep getting error messages "invalid outside procedure" I have limited knowledge in this (which is why I asked the question in the beginning) What I understood to do is create a module and enter the code you gave which gives me:


Option Compare Database
Option Explicit

Public varID As Long 'I assume that the ID is of type "Long"
Public GetVarID() As Long
GetVarID = varID
End Sub


That is the first error.

I also tried to enter the info on the form as you stated, but that results in another error which I am so locked up I can't get to reproduce.

And when I try to enter the code given for the query I get the error message "There was an error compiling this function. The visual basic module contains a sytax error. Check the code and then recompile it."

I realize the codes are because I have done this wrong. If you would take the time to help me get this correct I would appreciate it.

Regarding normalization, I can't post the table relationships because of privacy issues. But the reason for so many forms is because each one does something different. This code I am using now is off a button and does the same action no matter what form it is used on.

Sue

 

[Design by Sue]

Wayne Ryan - sorry but can you please tell me where your code would go??

Thanks

 

[Mihail]

Sorry for this. I forget a word. I'm an old man and the Alzheimer...
Public GetVarID() As Long
should be
Public Function GetVarID() As Long

 

[MSAccessRookie]

Is there such a thing as code for "or" in the criteria field of an update query? I can use the same query to update a table based on which of 5 forms happens to be opened at the time. I would like the criteria to read something like:

WHERE ((([TBL 1].[ID]) Like [Forms]![FRM 1]![ID] Or [TBL 1].[ID]) Like [Forms]![FRM 2]![ID] Or [TBL 1].[ID]) Like [Forms]![FRM 3]![ID] Or [TBL 1].[ID]) Like [Forms]![FRM 4]![ID] Or [TBL 1].[ID]) Like [Forms]![FRM s]![ID]));

So it only uses whichever of the forms are open at the time. The only way I could see to do this is create a different query for each of the forms but wondering if I can streamline this.

Thanks
Sue

Sue,

I had a problem similar to this one (I think). The project used two Tables to store information related to Equipment that the company used. The first Table contained Standard Equipment Information that was similar for all Equipment, while the second one contained Equipment Characteristics that were different. The Equipment Characteristics Table contained a Field for each Characteristic for all possible Equipment Types that were used, and in all records, only related information had values, and as a result, most of the infomation was blank. The Tables were related on a 1:1 basis, and were split due to both the size of the Characteristics Table, and the frequency of use of the Common Equipment Information.

There was a Main Form displaying the Standard Equipment Information, and a Sub Form displaying the Equipment Characteristics. The Main Form had a Combo Box allowing selection of the Type of Equipment. Once the Type was selected, any one of 15 different Characteristics Sub Forms could be displayed. All of the Sub Forms used a commmon Equipment Number ID that referred to the ID on the main form.

This proved to be more than a little too slow, so I changed it to a Tabbed Form that had a single datasource based on the Equipment Characteristics Table. Each Tab has its own Constant value based on its Tab position. A procedure was created to determine the Selected Tab ID and display the data for the Selected Tab based on the contents of the Equipment Type Combo Box.

In the ON Open Event for the Form, the contents of all of the Tabs were hidden. If the Form was opened for Update, then the Equipment Type Characteristics display procedure was called.

In the ON Change Event for the Equipment Type Combo Box, the Equipment Type Characteristics display procedure was also called.

This allowed the proper Characteristics to be displayed at all times.

Would something like this work?

-- Rookie

 

[Design by Sue]

Mihail - thank you - will try again tomorrow.

Rookie - don't think that will work in this instance but thanks

Sue

 

[WayneRyan]

Sue,

Sorry, been away for a while.

This should be a simple process.

Make a Query ("YourUniversalQuery") that does whatever its supposed to do AND
use a where clause for any one of your Forms.

Then on EACH of your forms, put the following command button and code substituting
the name of each form for the [YourForm] in the code.

That should do it.

Private Sub cmdTest_Click()
Dim qdf as DAO.QueryDef

set qdf = dbs.QueryDefs("YourUniversalQuery")
qdf.sql = Mid(1, Instr(1, qdf.sql, "Where ") - 1) & " Where [TBL 1].ID Like [Forms]![YourForm]![ID]"
qdf.close

doCmd.OpenQuery "YourUniversalQuery"
End Sub

Wayne

 

[Design by Sue]

Thank you all - Mihail that worked perfectly and you have taught me something very valuable that I will be able to use in the future,

WayneRyan - thank you also - will keep this also in mind for a solution down the road

Sue