Record count in SubForm and how i can .... (1 Viewer)

parara

Registered User.
Local time
Today, 22:22
Joined
Oct 15, 2007
Messages
16
Hello ,
I have a subform "PurchaseDetails" into a form "Purchase"
How i can count the records (how many) are in subform.
(i am using
i = Forms![Purchase]![PurchaseDetails].Form.RecordsetClone.RecordCount)

Is this correct?

And how can reffering into one of the record (from the begin to end)

Thanks
 

ajetrumpet

Banned
Local time
Today, 14:22
Joined
Jun 22, 2007
Messages
5,638
Hello ,
I have a subform "PurchaseDetails" into a form "Purchase"
How i can count the records (how many) are in subform.
(i am using
i = Forms![Purchase]![PurchaseDetails].Form.RecordsetClone.RecordCount)

Is this correct?

And how can reffering into one of the record (from the begin to end)

Thanks
This is kind of vague. You can count the records in a subform with DCOUNT. But, what do you want to do with that number? Is it for display? The .Recordcount Property can be used to display the count of a subform as well.
 

parara

Registered User.
Local time
Today, 22:22
Joined
Oct 15, 2007
Messages
16
Hi ,
Each record in subform has information (IDProduct , Product , Quantity )
I want to store the Quantity for each IDProduct (each record) in a table.
I can do it for one record , but what i have to do when records are more.
When i take information from first record , i must moved to next record to take information , e.tc.
 

ajetrumpet

Banned
Local time
Today, 14:22
Joined
Jun 22, 2007
Messages
5,638
Hi ,
Each record in subform has information (IDProduct , Product , Quantity )
I want to store the Quantity for each IDProduct (each record) in a table.
A subform usually exists because a table exists, so I don't understand what you're trying to say here.
I can do it for one record
Do WHAT, exactly?
When i take information from first record , i must moved to next record to take information , e.tc.
Take what information? Can you provide any more specifics on this whole process?? I can't get a picture of what you're doing...sorry.
 

parara

Registered User.
Local time
Today, 22:22
Joined
Oct 15, 2007
Messages
16
Ok ,
In SubForm i have the fields IDProcuct , Product , Quantity (these fields from table "PurchaseDetails").
When i have one record (one row) with these information and save this record , i take the number in Field Quantity and save it in another table. This i can do it.
NOW , how can take the "quantity" from next record (next row in subform) , and then next , and next , until end of recordset in subform.
 

boblarson

Smeghead
Local time
Today, 12:22
Joined
Jan 12, 2001
Messages
32,059
Just an FYI for you parara - You are definitely not taking advantage of the power of a relational database if you are storing the same data in another table. This is not normalized properly then.

I'm going to assume that you are doing this for reporting purposes, but I would ask the question why you can't use a query instead to get what you need because a query can be used anywhere where a table is used.
 

ajetrumpet

Banned
Local time
Today, 14:22
Joined
Jun 22, 2007
Messages
5,638
Wonderful explanation!! But, to get the right answer, here a few more questions...

**How are you saving the field quantity in another table? What method do you use??
**Where are these values being copied to in the other table (i.e. - same field, separate records, etc...)??
**You mentioned something earlier about a Recordcount. Where does that play into all of this?? Copying table values to another table is not related to the Recordcount property, unless you haven't indicated this yet.
 

parara

Registered User.
Local time
Today, 22:22
Joined
Oct 15, 2007
Messages
16
Hello ,
The method i used is

If Forms!Purchase.PurchaseDetails!Quantity <= 0 Then
Forms!Purchase.PurchaseDetails!StockRoom1 = Forms!Purchase.PurchaseDetails! StockRoom1
Else
Forms!Purchase.PurchaseDetails!StockRoom1 = Forms!Purchase.PurchaseDetails!StockRoom1 + Forms!Purchase.PurchaseDetails!Quantity
End If

Where Field "StockRoom1" is a part of table "Products".
In subForm i use qwery with fields from two tables.One of these fields is StockRoom1.
I want to increase the value of this field "StockRoom1" by value in field "Quantity".
The code above it's OK from the first record (first row) in subform. I can't found the way to do the same with the other record (if exist).
I don't know how Recordcount works. I was think that this command will solve my problem.
 

ajetrumpet

Banned
Local time
Today, 14:22
Joined
Jun 22, 2007
Messages
5,638
The method i used is

If Forms!Purchase.PurchaseDetails!Quantity <= 0 Then
Forms!Purchase.PurchaseDetails!StockRoom1 = Forms!Purchase.PurchaseDetails! StockRoom1
Else
Forms!Purchase.PurchaseDetails!StockRoom1 = Forms!Purchase.PurchaseDetails!StockRoom1 + Forms!Purchase.PurchaseDetails!Quantity
End If
From this method that you outlined, I can see what you're doing, although I'm not sure why a quantity would ever be less than 0??? But anyway, the following information should help you solve this...

** Updates made to queries result in the same updates made to the underlying table(s).
** Queries are not updateable if they contain functions. (Expressions do not affect the updateable property).
** A form is a form, and a form has a recordset, regardless of weather it is a subform or not. Subforms are still just forms. The only exception to this is when you use the "auto-object" feature in Access. If you make an auto-form from a primary table in a one-to-many relationship, the actual "subform" that is displayed has different qualities than, say, a "drag-and-drop" subform. Referencing is different too. I believe you have to reference the actual table instead of the displayed "subform".
** You can update values the same way you're doing it, but you're doing it at the form level. It is much easier to navigate through the form's recordset, check the condition, and perform the update. Remember that forms are just display tools, not data storage devices. To update the sub, I would use this...
Code:
Dim rs as recordset
  set rs = forms!Purchase.PurchaseDetails.Form!.recordsetclone

  with rs
  .movefirst

    do until .eof

      if !quanitity > 0 then
        .edit
          !StockRoom1 = !stockroom1 + !Quantity
        .update
      end if
      .movenext

    loop

  end with
In the code you provided, there was a little redundancy. You don't need an ELSE section of an IF condition. I consolidated your code into 1 statement, instead of two. I just used the statement that would change the !StockRoom1 field if it was true. If there is no ELSE in an IF statement, and the IF condition is not true, the code will skip it and keep right on running.

Keep in mind too, that this loops through the subform recordset. The fact that a query is the basis for the recordset doesn't matter, unless of course there is a function in it for one of the fields. If there is, you will get an error saying the recordset is not updateable. Anyway, I hope this does something for you...
 
Last edited:

parara

Registered User.
Local time
Today, 22:22
Joined
Oct 15, 2007
Messages
16
Hellos ,
Yesterday i test your code. Works greate. Thank you very much.
Just a small problem.
In a row (a record in subform) i have 5 fields. When i put value in fields and the cursor is on the last fields , the code works correct and the value stored.
When i put value in last field and then change row (press enter key for example , or tab key) the next record is empty. The code dont work . I can't undersand why.
Anyway thanks again for your help.
 

ajetrumpet

Banned
Local time
Today, 14:22
Joined
Jun 22, 2007
Messages
5,638
Hellos ,
Yesterday i test your code. Works greate. Thank you very much.
Just a small problem.
In a row (a record in subform) i have 5 fields. When i put value in fields and the cursor is on the last fields , the code works correct and the value stored.
When i put value in last field and then change row (press enter key for example , or tab key) the next record is empty.
I didn't think you were going to put values in manually. Wasn't that the purpose of the code? Or are you just typing in the "quantity" value, so the code will work?

The placement of the cursor has nothing to do with the code.
When you move to the next record, it is empty? Explain more...

Next subform record? Main form record? What is empty? Certain fields?

If you can, post the database here.
 

parara

Registered User.
Local time
Today, 22:22
Joined
Oct 15, 2007
Messages
16
To ajetrumbet

IN SUBFORM

In first record i select a product and then put value in field "Quantity" . If cursor stay in this field the code works. The "Quantity" value saved in a table , and also increase the "StockRoom1" field in another table.

Now , when i change record (in subform) and dont select any product (in the second row) , the code dont work. Maby cause the record is empty.

Thanks For your help.
Now another one question for you.
I am trying to found a way to secure my db. First , i want to lock the db. I dont want user make changes in any table , form , query in db. User must work only with forms not make changes on them.
Second i want to make an entry form which user must be give name and password to connect to db. Only Admin user make changes in forms,query,tables.

Have you any idea about?

ps. If you want i can quote my db , but it has many greek fonts.
 

Users who are viewing this thread

Top Bottom