HELP WITH FORM PLEASE

I must have lost my mind because I can't figure this out:

tblShows has a one-to-many relationship with tblAgilityRec.

I have a form based on a qry that includes tblShows and various other
tables. I added a button to the form that opens a form based on a qry
that only includes the tblAgilityRec. The code is:

Dim stDocName As String
Dim stLinkCriteria As String

Select Case Me.CompType
Case "Agility"
stDocName = "fsubAgilityRec"
Case "Rally"
stDocName = "frmRallyRecord"
End Select
stDocName = "fsubAgilityRec"

stLinkCriteria = "[ShowNo]=" & Me![ShowNo]

stDocName = "fsubAgilityRec"
DoCmd.OpenForm stDocName, , , stLinkCriteria

My problem is that when I attempt to add a new record to the form
"fsubAgilityRec" I get a message that reads:

"You cannot add or change a record because a related record is required
in table 'tblShow'.

What am I doing wrong here?

If "ShowNo" is a taext value ... try:

stLinkCriteria = "[ShowNo]='" & Me![ShowNo] & "'"

R. Hicks

--
Ricky Hicks - Access MVP

"sljack63" wrote in message
ups.com...
I must have lost my mind because I can't figure this out:

tblShows has a one-to-many relationship with tblAgilityRec.

I have a form based on a qry that includes tblShows and various other
tables. I added a button to the form that opens a form based on a qry
that only includes the tblAgilityRec. The code is:

Dim stDocName As String
Dim stLinkCriteria As String

Select Case Me.CompType
Case "Agility"
stDocName = "fsubAgilityRec"
Case "Rally"
stDocName = "frmRallyRecord"
End Select
stDocName = "fsubAgilityRec"

stLinkCriteria = "[ShowNo]=" & Me![ShowNo]

stDocName = "fsubAgilityRec"
DoCmd.OpenForm stDocName, , , stLinkCriteria

My problem is that when I attempt to add a new record to the form
"fsubAgilityRec" I get a message that reads:

"You cannot add or change a record because a related record is required
in table 'tblShow'.

What am I doing wrong here?

Oops! Figured it out. I added a field to my form "fsubAgilityRec" for
the "ShowNo" field with a default value of
"=[Forms]![frmShows]![ShowNo]"

HTH others.

Try the following nested IIF in the Field row, in the QBE grid:

Adj_Item_Price: IIf ([price_calc]=1, [item_price]+[option_price], IIF
([price_calc]=2, [item_price]-[option_price], [item_price]))

To be honest, I'd probably just write a custom function, because nested IIF
can get tricky to troubleshoot, although this one isn't too bad. If you are
comfortable with the idea of using VBA code, I can work up a quick procedure
for you.

Tom

http://www.access.qbuilt.com/html/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"sljack63" wrote:

I've inherited a MS Access product database with item options. I'm trying to
create a price list/catalog based on the product database:

The fields in the Query a
A. Item_Price (The price of the item if no options are given)
B. Price_Calc (The pricecalc field has several values 1=Add, 2=Subtract,
3=None, 4=Use Item Price)
C. Option_Price (The amount you alter the Item_Price based on Price_Calc)
D. Adj_Item_Price (The price of the item with options)

I need to calculate the price; however, I don't know how to use the
Price_Calc field. Here's what I'm trying to get to:
IIf ([price_calc]=1, [item_price]+[option_price]=[Adj_Item_Price], OR
IIf ([price_calc]=2, [item_price]-[option_price]=[Adj_Item_Price],, OR
IIf ([price_calc]=3, [display item_price], OR
IIf ([price_calc]=4, [display item_price]

Any suggestions?

Thank you,