form recordset

[Drunkenneo]

have a table of name, order id, amount
I need to generate a form such a way of the name is appearing 6 times the name should be initial fname and last name for 7th name and ahead and also appears 12 times then it should be divided as initial fname and lname of more than 6 and fname and initial last name in other more than 12 entries, i want to display same in my form pls help.

 

[CJ_London]

Can you post some sample data from your table and also an example of what you want it to look like.

 

[Drunkenneo]

Can you post some sample data from your table and also an example of what you want it to look like.

Thanks for response

Card|Orid|name|amt|telno
123 12 aa 20 123
124 12 aa 20 123
125 12 aa 20 123
126 12 aa 20 123
134 12 aa 20 123
125 12 aa 20 123
127 12 aa 20 123

This is a simple case i am taking where i need to display like
Card|Orid|name|amt|telno
123 12 aa 20 123
124 12 aa 20 123
125 12 aa 20 123
126 12 aa 20 123
134 12 aa 20 123
125 12 aa 20 123
127 12 aA 20 123
After 6th entry the name should change..... did you got my pt...

 

[CJ_London]

I regret I still don't understand. Access is not case sensitive so there is no difference between aa and aA which as far as I can see is the only difference.

Please give a clearer example

 

[Drunkenneo]

I regret I still don't understand. Access is not case sensitive so there is no difference between aa and aA which as far as I can see is the only difference.

Please give a clearer example

here after 6th entry i need the name need to be given in different form, eg my name is Abc Xyz, after 6th entry it need to be A Xyz i.e inital fname and surname

due to order formalities the amt should not exceed certain amt

 

[CJ_London]

I'm sorry, I simply do not understand what you want to do. You need to try and explain much more clearly. The example in your last post is different to the earlier post - you now have firstname lastname where the first character changes whereas before it was a single name where the last character changes.

 

[Drunkenneo]

CJ_London, its pretty simple thing to know or in understanding, that if i am asking the name in different format i.e full First name and full last name (have in table which will be displayed) and with a condition, it should be initial of First name and full last name.

If you see clearly in the previous example i have shown case sensitive for the format...

 

[CJ_London]

OK, so my understanding now of what you are trying to achieve is the following:

You have table with say 10 records
In each record there is a first name field and a surname field each with an identical value (firstname=Abc, surname=Xyz)
you want to display the records on a form (i.e. not change the underlying data)
If a condition is true the first name appears in full (Abc)
If the condition is false the first name appears as an initial(A)
The condition is that the rowcount <=6

If the above understanding is correct I need to know more:
What determines the order of records?
Is there a unique ID in the table which identifies each line, and if so, what is it?

 

[Drunkenneo]

OK, so my understanding now of what you are trying to achieve is the following:

You have table with say 10 records
In each record there is a first name field and a surname field each with an identical value (firstname=Abc, surname=Xyz)
you want to display the records on a form (i.e. not change the underlying data)
If a condition is true the first name appears in full (Abc)
If the condition is false the first name appears as an initial(A)
The condition is that the rowcount <=6

If the above understanding is correct I need to know more:
What determines the order of records?
Is there a unique ID in the table which identifies each line, and if so, what is it?

Yes, you are getting it right. order of records is determined by reference number and that is the only unique number in the table.

 

[CJ_London]

So going back to your post 4, there is a duplicate in the data (card=125) - is this a typo?

Card|Orid|name|amt|telno
123 12 aa 20 123
124 12 aa 20 123
125 12 aa 20 123
126 12 aa 20 123
134 12 aa 20 123
125 12 aa 20 123
127 12 aa 20 123

Assuming this is a typo, your code should be something like this:

SELECT CARD, Orid, [COLOR=red]iif(Dcount("[Card]","MyTable","[Card]<=" & [Card] & "AND [Firstname]='" & [FirstName] & "' AND [LastName]='" & [LastName]),[FirstName],Left([FirstName],1)) AS FName[/COLOR], LastName, Amt, Telno 
FROM MyTable

 

[Drunkenneo]

Thanks for the response, but the result isn't seem to working

 

[CJ_London]

I've reviewed the code and cannot see an issue and unfrotuantley 'isn't working' doesn't give me a clue.

Please post the code and table/field details you are actually using.

Table/field details to include

name
type (text, Long, etc)
whether there are nulls in the data

 

[Drunkenneo]

Attached is the sample file of my table, there may be typo

 

[CJ_London]

And the code you are using?

 

[Drunkenneo]

This was previously used which is definitely not a solution....i haven't got anything yet so keeping it in the same way...

Private Sub Form_Load()
********************REDEMPTION FILE IMPORT***********
On Error GoTo Err_cmd_vimport_Click:


Dim srs As DAO.Recordset
Dim srs1 As DAO.Recordset
Dim i, int1 As Integer
Dim str_qry, str2, str3 As String
Dim str1 As String

'Recordset for all the records whose payment has been made
str_qry = "SELECT * From Activation_Log"
Set srs = CurrentDb.OpenRecordset(str_qry, dbOpenDynaset)
str1 = srs![order_number]
Vcount = srs.RecordCount
Do Until srs.EOF
Set srs1 = CurrentDb.OpenRecordset("SELECT * From Payment_Log WHERE (((Payment_Log.Paid)=True)) and order_number = " & str1, dbOpenSnapshot)
i = srs1.RecordCount
Do Until srs1.EOF
If (i > 6) Then
srs1.MoveFirst
Form.Ecard = srs1!Ecard
Form.Ereference = srs1!Ereference
Form.Name = srs1!Name
Form.Upload = srs1!Upload
Form.contact_number = srs1!contact_number
i = i - 1
Else
Form.Ecard = srs1!Ecard
Form.Ereference = srs1!Ereference
Form.Name = srs1!Name
Form.Upload = srs1!Upload
Form.contact_number = srs1!contact_number
End If
srs1.MoveNext
Loop
srs1.Close
srs.MoveNext
Loop
srs.Close

error handling
Exit_cmd_vimport_Click:
Exit Sub

Err_cmd_vimport_Click:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume Exit_cmd_vimport_Click
End Sub

 

[CJ_London]

I'm sorry - this is nothing like the code I provided and has a completely different objective to what I clarified with you. I suggest you start a new thread starting from your last post.

I'm afraid I cannot help any further

 

[Drunkenneo]

Thanks for yor effort, this isn't your code, this is what i tried, as you have formatted a sample code for me, if you can format this at your end also that will be a great help, thanks.

 

[Drunkenneo]

Attached will explain most of things...

 

[Drunkenneo]

Attached Now

 

[CJ_London]

What I don't understand is why you cannot use my solution - all you have said is 'it doesn't work' without further explanation and then provide a much more complex solution to try and resolve - again without any explanation as to what the problem is other than 'it doesn't work'.

I can tell you that your srs1 loop won't work - it looks like it is in an infinite loop if i>6.

Also you are assigning to the same form values without advancing to the next row of the form recordset.

And with regards this

Form.Name = srs1!Name

Name is a reserved word so almost certain this line won't work

Finally you have two field names (Upload and Paid) which do not match to what you have supplied as data and the result you are trying to achieve also does not match the supplied required result.

Now you have supplied more information I have restated my code using your column names in your source data (with the exception of Upload and paid which is not there)

SELECT iif(Dcount("[ECard]","Payment_Log","[EReference]<=" & [EReference] & "AND [contact_first_name]='" & [contact_first_name] & "' AND [contact_Last_name]='" & [contact_Last_name] & "' AND Paid=True")<=600,[contact_first_name],Left([contact_first_name],1)) AS FirstName, contact_Last_name, order_number,email_address, contact_nmuber, ecard, ereference,[upload amount] 
FROM Payment_log WHERE Paid = True