Urgent Dmax Help Required

[Ashfaque]

Hello gentlemen,

My main form contains a Entry_No (Text – Not duplicate) field. I used following code in the same form in a command button’s OnClick event to increment integer value of Entry_No to “Issue-1”, Issue-2”, “Issue-3” & so on.

Dim strmax As String
strmax = DMax("[Entry_No]", "T_Drug_Receiv_Head")
Me!Entry_No = "Issue-" & Right(strmax, _
Len(strmax) - _
InStr(1, strmax, "-")) + 1

No problem with above code. It works fine.

The record source of the main form initially was based on query which I removed later. I placed following code to display only the last record while form opens (on Open event). This is a try due to very large table and I don’t want my form / query to load all 90,000 records into the memory at one time that takes time.
I placed a unbound text box (TxtMaxt) to disiplay Entry_No field of last record of the table which is ok.

Txtmax = DMax("[Entry_No]", "T_Drug_Receiv_Head") ‘ This is OK.

Dim NSSQL As String

NSSQL = "Select * from T_Drug_Receiv_Head where [Entry_No] = Txtmax"
Me.Form.RecordSource = NSSQL
Me.Refresh

When I open the form, it asks me ‘Enter parameter value’ for ‘Issue’. When enter something then dialog closes and form appears with blank record except showing displaying Entry_No field of last record of the table in the Txtmax unbound text box.

When I removes code from on open event and selects query that was set before as record set, it works.

Where might have gone wrong?

With kind regards,
Ashfaque

 

[john471]

....Entry_No (Text – Not duplicate) field....

NSSQL = "Select * from T_Drug_Receiv_Head where [Entry_No] = Txtmax"

Where might have gone wrong?

NSSQL = "Select * from T_Drug_Receiv_Head where [Entry_No] = " & """" & Txtmax & """"

you concatenate the contents of the variable, rather than the name of the variable, into the SQL. Because the underlying field is type Text, eyou also need to encapsulate it in quotes.

BTW - are you certain your DMAX is working exactly as desired? Sort order on the "text" field may yield undesired results. (see attached image for example)



HTH

Regards

John.

 

[Ashfaque]

I really appreciate your immediate response John. Thanks.

You are abdolutely correct.

I started entering invoices and it accepted upto only "Issue-10". When adding next...producing erroras shown in pic.

Moreover, it (Dmax) is not working in sort order. Again you are right.

What you advice ? is there any solution for this ? But first about my unbound text box (TxtMax) which I intended to display last record but now it is displaying only 9th record while there are 10 record in the table.

Have a look at the pic and please extend your help.

 

[john471]

Firstly - I strongly! recommend that you avoid the use of special characters and embedded spaces in object names (e.g. names of tables, fields, queries forms, reports, text boxes etc etc)
So I would rename the "Entry_No" field to "EntryNo" or EntryNbr or something without the underscore. Apply the same principle to all field names. This is just "good practice". It may cause you no harm at present, but it could come back to bite you later.

Secondly - if you want to display the value as "Issue-"&# and that part of the field is static in this table, that is one thing, but it does not necessitate that you store it thus. Therefore I would change the field type to Number (Long Integer), and store only the numeric / variable part, then change the text box's control source to display ="Issue-" & [EntryNo]

By changing the field type to Number (Long Integer), your DMax will then return the result you are desiring

 

[Ashfaque]

Thanks again John,

I will let you know shortly the outcome.

Thanks a lot again.

With kind regards,
Ashfaque

 

[Ashfaque]

Well, I have removed all the underscores from the tables, queries, Forms and reports as well. Compiled all the code (all objeects) and found no error.

It is runing smoothly. But when I placed = "Issue-" & EntryNo as record soruce in the EntryNo textbox, it produces error as attached in pic. Moreover, the subform (with one to many relationship) is also producing whilte screen. (Ref pic)

But if I remove the = "Issue-" & EntryNo ..then works fine. Then I am not getting the entryno field data as "Inssue-1", "Issue-2" etc. It will give me only integer. Do you think I have to set it in the format (property of table) itself as "Issue-" OR constuct a public function ?

Please advise

With kind regards,
Ashfaque

 

[Rich]

You only need to store the EntryNo, use a label to display "IssueNo", you don't need to store the text "IssueNo" anywhere

 

[john471]

Your problem may be the name of the text box being the same as the name of the data field. I would ensure the text box object (as distinct from the data field) had a unique name e.g. txtEntryNo, then the control source of the text box might be:-
="Issue-" & [EntryNo]

Of course, what Rich says is also true; you could use a label to show the word "Issue", and have the txt box only display the variable number.

HTH

Regards

John

 

[Ashfaque]

Yeah...

I changed the text box name to differ it from the data field name. The new name is now TxtEntryNo and also changed in property of footer form (Link Master Field) to TxtEntryNo. And set control source of the main forms text box as = "Issue-" & EntryNo

It appears ok now......It neviagates record nicely. but when I add the new record it produces another error in code. Please check the below code line.

Me.TxtEntryNo = DMax("[EntryNo]", "T_Drug_Receiv_Head") + 1

saying 'You can't assign a value to this object.'

With kind regards,
Ashfaque

 

[john471]

That is because you need to update the data field, however you are trying to update the text box.

Me.EntryNo = DMax("[EntryNo]", "T_Drug_Receiv_Head") + 1

 

[Ashfaque]

I tried (also) the way you described John, It says Compile error : "Method or Data Member Not found"

Even I tried removing square brackets of EntryNo but this does no effect.

I don't understand where is going wrong ?

Ashfaque

 

[Ashfaque]

I just got it John,

It should be :

Me!EntryNo = DMax("[EntryNo]", "T_Drug_Receiv_Head") + 1

There should be excalamtion mark after Me and not the DOT (.)

But I thanks for your real help you extended to me.

With kind regards,
Ashfaque

 

[Ashfaque]

The main form works fine but there is another problem now.

Sub form which has one-to-many relationship with main form on EntryNo field, producing error.

The error is with field EntryNo.

Please advice.

Regards,

Ashfaque