Subform Error Issue

Mar 15, 2008

Hi, i have a form which shows stock levels. It works by the user entering a value and pressing search, then one query returns the data on the product from the stock table (so that it can be edited), and another query returns a "total of how many have been sold" in the form of [sumofunitquantity]. by subtracting the number sold on the one subform from the number bought in on another, i get the number in stock.

my problem is, because of the way this system works, if the item has been bought in but none sold, it returns an #error in the [in stock] field because the return on the [sumofunitquantity] is null.

when the query is run in this situation, the product information is shown along with the number bought in, but the field for the number sold remains invisible on the form, so the calculation cannot be completed.

i have tried using a code such as this:
Private Sub Combo63_Change()
If IsNull([frm_stocksubsub].Form![SumOfUnitQuantity]) = "" Then
[frm_stocksub].Form![Text23] = 0
Else
[frm_stocksub].Form![Text23] = ([frm_stocksub].Form![StockBoughtIn] - [frm_stocksubsub].Form![SumOfUnitQuantity])
End If
End Sub

but with no success. basically i want it so that if there is a null value in no. sold, the stock value is 0.

any ideas?

thanks
jjames

View Replies


ADVERTISEMENT

(Error 3022)The Changes You.... Error In Subform

Dec 18, 2006

hi guys i was wondering if you can help me this is my code: i have a main form with this code, this form contains a subform linked by the All_PricingID

Set rst = CurrentDb.OpenRecordset("tblAll_Pricing") 'main table
' adding data to the table
rst.AddNew
' Main table
rst!All_PricingID = Me.txtPricingID 'Main table pk
rst!MainContract_ID = Me.cmbMainContract 'combo box in parent form
rst!ItemNumber = Me.txtItem 'Main form text
rst.Update

'sub Table
Set rst2 = CurrentDb.OpenRecordset("tblPricing") 'sub table
For varItem = 0 To Me.lstsubContracts.ListCount - 1 'this is a list in the main form
'--- loop through all the items in the list box and create a new row in the subform for each subcontract in the listbox lstSubcontracts.
rst2.AddNew
rst2!ID = Me.All_PricingID 'sub table foreign key
rst2!SubContractID = Me.lstsubContracts.Column(0, varItem) 'sub table
rst2.Update
Next varItem
'--- close the tables
rst.Close
rst2.Close
Set rst = Nothing
Set rst2 = Nothing


the subform appears correctly with the rows i wanted added but i need the user to be able to edit a column in the subform for the rows just created (my form is on datasheet view). but everytime i move to cursor into the subform, i can't even scroll up and down.

i keep getting an error that says :

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. (Error 3022)


but when i check my tables tblAll_Pricing and tblPricing , everything is inserted correctly according to my recorset above, do you know why this is happening? and why i am not able to edit my subform. my subform allowsedits and additions.


help!!

View 2 Replies View Related

Subform Error!

Jan 18, 2008

Hi all!

We have a form in our application(developed in Access 2003) with numerous tabs in it. On click of each tab a subform is displayed with relevant details.

Each of the subform is mapped to a table in turn.
When the subform is opened it shows the all the values of the table.
But when the form is opened (in Access 2007), it shows only one record in the subform.

The application works fine in MS Access 2003 showing all the records in the subform.

Suggestions please!!!!!

View 2 Replies View Related

Error When Trying To Add A Second Record To Subform

Nov 24, 2005

Hi guys,

i have a form which has a sub-form on it. each one is bound to a different table. the main form has a one to many relationship with the subform, but every time i try to add a new record to the subform i get an error say that you cant insert a duplicate foreign key.

not sure how to solve that as sql enterprise manager and access do seem to have the option to allow duplicates on the foreign key coloum.

View 3 Replies View Related

Error When Opening SubForm!!

Apr 18, 2007

hello,

I have a subform which opens good with Access 2003 but now that I updated to Access 2007, it won't open. It gives me a "type mismatch in expression" error. Why does this happen??

thank you.

View 2 Replies View Related

Strange Error With Subform - Please Help

Sep 19, 2005

I have a bound form with a bound subform which is in continuous or datasheet view. I have been experiencing a strange problem of late. After i complete the first record of the subform and then move on to the second record , sometimes i get strange messages like :-

1) The Microsoft Jet Database Engine Stopped the process because you and another user are attempting to change the data at the same time.

2) Invalid Argument

3) Unrecognised database format c:windows empjet(something).tmp

4) Not a valid bookmark

These errors once they appear wont go away even if you try to ignore them. they just keep coming and each of the columns has an "#error# written in it. you have to close the module and start again. Whats more if you enter exactly the same data again, they do not show up again i.e. random errors. and it happens only when you complete the first record.

To clarify, these errors appear even in a single user environment. i do not change the recordsource or link fields properties at run time for the subform or mainform.

is there anything i have overlooked in form design / code ? Please help me out.

View 6 Replies View Related

Subform Is Not Opened Error

Nov 4, 2005

I have a subform inside a Form.
When I'm trying to upadate the subfrom from an event triggered by a control in Form I get the error message that subform is not opened.Why is that?
I've tried commands MoveTo or Select (subform) before update command in order to force update command to understand that subform is opened but without any result.
I guess is a common problem but I can't find the solution. :(

View 1 Replies View Related

Active X Error On Subform

Feb 13, 2006

Hi all,

I have a form with two sub forms and when I enter data into one of my subforms I get this error?


A Problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control.

Close the OLE server and restart it outside of Microsoft Access. Then try the original operation again in Microsoft Access.


I've checked the relationship and linked child / master fields and it looks ok. Does anyone know what could be causing this?

I am getting this error on a couple of different PC's running WinNT4 and Win2000.

Shutting down doesn't help and I don't get this with other databases. When I open the form (subform2) on its own I can enter data, just not when it's part of the main form?

View 1 Replies View Related

Error When Open Subform

Aug 14, 2006

Hi all,
I have a form with a button on it, that when clicked it will open a subform on the same form. But, when I clicked this button, it gave me this error message:

The expression On Click you entered as the event property setting produced the following error: A problem occurred while [Database Name] was communicating with the OLE server or ActiveX Control

For more details on this error message, see the attached file.

Any attempt to help will be highly appreciated.

Sorry for bothering you.

View 2 Replies View Related

Subform Read-only Error :(

Sep 6, 2006

Well, I've got a multiple stage form structure. Data tables, in case it matters.

The two stages that we're interested in right now, let's call them Stage1 and Stage2. Anyway, Stage1 is controlled by a query (Query1) of a table (Table1). And Stage2 used the be the subtable of the Table1. It didn't work, with the same error.

"This property is read-only and can't be set." is what I see if I try to enter data into Stage2. I can enter data, but none of it is saved. The data entered into Stage1 -> (Query1 -> Table1) works perfectly.

Just for the record, Table 1 and Subtable are related, with referential integrity. How do I fix this?

View 2 Replies View Related

Subform Linking Error

Dec 20, 2005

Could someone please help me trouble shoot this error:

"The Link MasterFields property setting has produced this error:'A problem occurred while Microsoft Access was communicating with the OLE Server or Active X Control'

I get the error when I try and select an item from a drop down list in a combo box on a subform that is linked to another form. The list is coming from a separate table.

When running the subform without opening the parent form I can choose from the combo box and do not get the error.

C

View 3 Replies View Related

#Error In Subform SUM Control

Dec 16, 2004

I have a control called TotalPaid in a subform Footer section that sums up payment Amounts in the Details section. TotalPaid's control source is simply =SUM([Amount]).

That's it. Nothing tricky, I'm not summing up calculated controls or anything...

PROBLEM: TotalPaid frequently displays "#Error".

I can determine absolutely no reason for this. It is inconsistent, and is not specific to any certain subform records or mainform records. If I go to the next mainform record and then go back, the #Error will often disappear, to be replaced by the apprpriate SUM value.

Is this an Access 2000 bug?? I need to know what causes this and what I can do about, since the value in TotalPaid is VERY important to many of my other calculations. When it gives an #Error, my whole app becomes worthless.

View 4 Replies View Related

Forms :: Sum Error On Subform

Jul 29, 2015

I have a form that have a calculated field which generate a series of numbers. Correct and usuable numbers. Now, all I want to do this is to total (Sum)that numbers up at the Form Footer.. I have a Text Box control on the form footer for this. And formula on the Text box control is this.. =Sum ([Dhrs]*[Davg]). I got error everywhere on my form when I open the form..I have been trying using NZ Sum etc..

View 14 Replies View Related

Leaving Subform Error With Relationship

Feb 27, 2006

Hey guy im kinda new to this program so excuse me if my question is simplistic:I built a form for making Purchase orders. In the main form...a user enters a bunch of text/num info (name, address, ect)into the table. After that i have it tab to a subform..where a user can add items by line (as many as they like). All good till here: Once i go to click out of the subform...i get message "You cannot add or change a record b/c a related record is required in table 'UCT_Req'"My relationship has a 'one-to-many' link (the req number to the many line items). I have auto-numbers counting up in each table.My main problem seems to be attaching the entered line items to a specific record in the main Purchase order form...and then opening a fresh form afterwards. Any hints?Thanks folksSTeve

View 2 Replies View Related

Calling Procedure In Subform Error

Jun 9, 2006

I have a form, Salesperson, that has a subform Salesperson_SalesGraph. The SalesGraph subform is a pivot chart.

I'm having problems with trying to access items on the subform. The weirdest part is that it seems to work if i call the event from form_open, but doesn't work if i move the code to form_current. This won't work for me, as i need the function called whenever i change rows.

The error received is: You entered an expression that has an invalid reference to the property Form/Report.

First I had the code on the main form, accessing the chart object on the subform but was getting errors that i couldn't access the form property in the assignment, Set objChartSpace = Me.salesperson_salesgraph.Form.ChartSpace (i tried a number of differnet ways, with !'s, with Forms("SalesPerson_SalesGraph), etc.

Then i moved the code to the subform and now i'm just trying to call the publically declared sub with the line:

Forms!Salesperson!Salesperson_SalesGraph.UpdateLeg end
or
Me.Salesperson_SalesGraph.Form.UpdateLegend

The subform has a sub declared as:
Public Sub UpdateLegend()



Any thoughts?

View 3 Replies View Related

Subform Duplicates Error Message

Jul 28, 2006

Hi

I have a simple database. an articles table, authors table, categories table, not much else.

So one article can have many categories. To manage this, I have my 'articles' table (pk autonumber ID, plus other fields), a 'categories' table (pk autonumber ID, txt description), and I created a linking table 'article categories' autonumber ID, number articles (related to article ID in article table) and number categories (related to category ID in categories table)

Then I made a form for articles, with a subform for categories (taking the category field from the categories table).

1st problem - the category field in the subform appears as a text box, not a drop down list of category descriptions. I deleted it and added manually a combo box with the category field from the categories table as control source, telling it to store the value in the field 'category' in the 'article categories' linking table. This gives me the drop down list.

2nd problem - I select one category in the subform, when I try to select a second, it tells me "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

I checked the fields in the 'article categories' table and they are not set to 'no duplicates'.

The strange thing seems that I can enter this ok in the table, just not the subform.

I know this problem has already been discussed here, but I could not find an answer to my case in the previous posts, so I apologise if this is repetitive.

I think this is simple but I am a novice...

I tried to attach the database to this - both normal and then zipped but it says 'invalid file' (it is only 1mb).

I would be extremely grateful for anyone's help

thank you

Eleanor

View 4 Replies View Related

General :: Error Using A Subform To Navigate

Dec 30, 2014

Basically what I am doing is duplicating a split form. I am not using a split form because I want to be able to have better control over the bottom half, and I am giving the end user a slightly different view of the same information in the bottom pane.

I've created a form with a subform. When the user moves through the records in the subform, I want the parent form to navigate with it. Note the parent child relationship does not apply here.

I've almost got it working fine, but there is a bug. It seems to originate when the user clicks on the subform ID field while the cursor is the cross, like the Excel fill symbol. Now if the focus is on the subform where ID=2, when the user moves to the parent, the parent navigates away from the record for some reason.

I've noticed that the GotFocus event is firing multiple times, and some of the events are nested. I've tried to set a flag to prevent the nested events from firing, but the bug persists.

View 3 Replies View Related

Continous Forms Subform Error Message!

Jan 24, 2005

Continuous forms error.

Hi,

I have a database which displays one form as a subform of another form. Originally the subform was displayed in datasheet view, but now due to changes I am trying to make to the system I want to display this subform as continuous forms. Unfortunately in datasheet view, when a record of the main form has several records (ie enough to require scrolling down the list) in the subform I get errors when scrolling through these subform records.

The error message that access is displaying is "The Value you entered isn't valid for this field. eg you have entered text in a numeric field or a number that is larger than the FieldSize setting permits."

I thought ok, maybe the subform design was duff (I am taking over a system designed by someone else) and so created a new subform, but the error still occurs with a totally fresh subform. The error occurs even when viewing the subform on its own. With a lot of records displayed on screen in the continous forms view I get numerous occurances of the abopve mentioned error message box and access eventually crashes. Yet in datasheet view everything is fine.

It's incredibly annoying as I can't fathom out what is causing the problem and how to fix it and to go about what I am doing in another way (ie without using continous forms) will mean scrapping a whole days work. I also don't like to do things a different way just because I can't get it to work the way I'd like to.

If anyone has some solutions to this problem I would be eternally grateful!

Thanks in advance!

John

View 1 Replies View Related

Blank Subform No Current Record Error

Aug 22, 2005

Hi all.

I have a small bug here which I can't seem to find a way round.

I have a subform setup to show Engineers assigned to a call. In the subform header I have a Command to add new Engineers. When this is pressed the form is changed to a Data Entry form to allow additions. This all works perfectly where there is an existing engineer on the Database.

The problem is with calls where there is no current engineer assigned.

With these calls I get an error popup saying "No Current Record" before the Data Entry screen comes up. Once the Data Entry screen comes up then an ID is assigned correctly and you can input fine so I am really just looking to see if there is a way to suppress the No Current Record popup.

Any suggestions appreciated.

JC

View 3 Replies View Related

Error 2465 With Subform Naming Convention!

Jan 25, 2006

I've searched and experimented, but to no avail...

On a form (frmWorkOrder) , I want to “call “ a property on a subform (Named sfrmDocument). In order to disable that subform.

Thus far I’ve narrowed the error to:

Me!sfrmDocument.Form.Enabled = False
Me!sfrmDocument.Form.Locked = False


This seems to be the way that http://www.mvps.org/access/forms/frm0031.htm would handle it. Someone's suggestion and it seemed to work before ...

Maybe I’m reading it wrong!! At any rate, I get the error 2465 Application defined or object defined error.

Am I forgetting something ? Help!!

View 4 Replies View Related

Ambiguous Name Detected-error On Linking Subform

Aug 23, 2006

Hello,

I have a form and subform linked by the ID field (AutoNumber) on the Form and Record ID field (Number) on the subform. I have the field size on the Record ID field set to Long Integer.

When the form is opened, a message pops up "The LinkMasterFields property setting has produced this error: 'Ambiguous name detected: Add_client_info_Click' There was a macro set up with the name Add client info. However, that macro is not being used on either of these forms. I don't understand why this message is popping up.

Any ideas? I have checked each form, each form section and each field to make sure that macro didn't somehow get assigned, and I can't find it anywhere in relation to these 2 forms.

Thanks
Stacey

View 1 Replies View Related

#Name Error In Form Text Box--passing From Subform

Apr 20, 2007

Hi--I am working on a database which is a modification of the following MS Access 2003 Order Management template:
(URL address blocked: See forum rules)=CT101426031033

In my database, I have a subform called sbfOrderDetails, which in its Form Footer has a text box that sums the items in the order and is called OrderDetailsTotal.

I am trying to pass that value to a text box in the main form called OrderSubtotal.

I have three questions:

1) What is the proper syntax to use to get the value of the subform control into the OrderSubtotal control on the main form? I have tried various permutations and continue to get a #Name? error.

2) If you look at the expression in the Order Subtotal control of the Add and Manage Orders form in the original Order Management template, it looks like this:

=[Order Details Subform].Form!OrderDetailsTotal

When I try to replicate that in my database, I cannot--if I add brackets around my subform name and press Enter, Access adds brackets around everything in the expression, i.e. =[sbfOrderDetails].[Form]![OrderDetailsTotal]
(which returns a #Name? error). If I edit out the brackets I don't want and then press Enter, it puts the brackets back in. In other words, Access keeps trying to "fix" my expression by either having brackets around each item, or no brackets at all! This is really annoying; is there any way to turn it off??

3) When sbfOrderDetails is set to Single Form view, I can see the Form Footer (and the value in the OrderDetailsTotal text box as well). When it is set to Datasheet view, I cannot. Why?

Thank you very much,
lcnlit

PS>I cannot attach my database since I am a new poster, and the forum is disabling the URL of the Microsoft template as well.

View 3 Replies View Related

Forms :: DLookup Not Working In Subform - Keep Getting Error

Apr 23, 2014

I have a subform that has the Expense Code from the Expense Codes Table and I want to look up the Description (yes I know its not correctly spelled in the formula but it is in the table)

=DLookUp("Expense Discription","Expense Codes","Expense Code=" & [ExpCombo])

But I keep getting "error" returned

I have double checked spelling field names etc...

View 6 Replies View Related

Forms :: Getting Error With DSum Function In Subform?

Feb 16, 2015

How to use Dsum Function? Am using below following Dsum functions but they are not working and shown an error result.

=Nz(DSum("[Liters]","[SRB_SubformQry]","[Category]=MILK"),0)
=Nz(DSum("[Liters]","[SRB_SubformQry]","[Category]='MILK'"),0)
=Nz(DSum("[Liters]","[SRB_SubformQry]","[Category]<>MILK"),0)

View 3 Replies View Related

General :: Subform Enter Parameter Value Error

Aug 11, 2012

I have 2 form

main form = "TanggalF"
subform = "MerkFsubform"

I have a query with 2 field "Combo28" and "Combo30"

I use it in subform with query [Forms]![MerkFsubform]![Combo30]

Combo30 is combo box on MerkFsubform

If I open in subform "MerkFsubform" work, no problem

but if i open on main form "TanggalF" it get errror "Enter Parameter Value" Form!Merksubform!Combo30

View 3 Replies View Related

Forms :: Update Subform - Recordset Error

Sep 14, 2013

How it works: You search for a User, and then you search for a Serial.Now, what if I want to edit the table? (inside the subform, the results). I added an openRecordSet, and Parameters, but I still get the error saying that Recordset cannot be updated.Why? I opened and sent parameters but still fails.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved