count of collection records and also increment the value of collection number

[sireesha]

Hi,



I have a master/child forms. in master form sale_id,collection notes are placed. and in child form all collection details like collectionnumber,date,etc.,
child form look like datasheet view.
when i select the record from the list depending on the sale_id all the collection records shown in datasheet view form.
if no record is there ok. if more than one records are there in collections of sale_id.i need count of records in collection depending on sale_id
and also if i select one row in collection, notes to be shown for that record when i clik on shownotes button.

if more than one record in collection automatically it increment. how many collections for the sale_id. collection number would be increment.
how and also show notes depending on collection number and sale_id.

please help me as early as possible.

thank you
sireesha

[burrina]

txtRecordCount ' create a textbox with this name
=Count(sale_id) ' set as control source

If [Me.txtRecordCount] > 0 Then ' Place code where it needs to be
[Me.sale_id] = [sale_id] +1
End If


Not sure I understand what you are after, but hope this helps.

[sireesha]

Private Sub COLLECTION_LINE_NO_gotFocus()
Set myDBS = CurrentDb
SQLString = "select Count('Collection_line_no') from oldtractor_collections where sale_id=" & Me.SALE_ID


Set recSet = myDBS.OpenRecordset(SQLString, dbOpenDynaset)
p = recSet.Fields(0)


If Me.COLLECTION_LINE_NO.Value = "" And p = 1 Then
Me.COLLECTION_LINE_NO.Value = 1
Else
p = p + 1
Me.COLLECTION_LINE_NO.Value = p
End If
End Sub

the row is increment but wherever i place the cursor because gotfocus. already saved record is changed to increment value. and if selected saleid contains no rows the collection number should start from 1 but it placed 2. i need newly entered record should be incremented already saved records not changed.suppose 3 records entered 1,2,3 when new record is entered it should be 4.

manually entering the records collection no. sometimes collection number have duplicates entered so i want to use like this way but its not working.
please help me

thank you

[burrina]

It seems to me that you are telling it P=1 for no value but then p = p + 1 , which is it ? Conflicting code, You give p a value of 1 then also give it p + 1 and that is why you get a value of 2. To truly see what you are doing I would need to see the db, care to attach for evaluation?