Solved Change of field type DMax not working (1 Viewer)

Manicduck

New member
Local time
Today, 14:21
Joined
May 22, 2020
Messages
29
Good morning all,

Sorry to bother you again, yesterday I asked for help with sequential numbering using DMAX, it turned out that I had set the field type to text and DMAX stopped updating the sequence when it got to 10. Following the kind advice given, I reset the field type to number and applied the required formatting to the cell to display the required leading 000s. Then with a tweak of the code offered by @bob fitz, all worked well.

Unfortunately, when I opened the project this morning, I found that changing the field type of Report_ID to number has stopped another part of my project from working. I have a single combo box: cboChooseReport, which returns the values of the Report_ID from qrySelectReport , as there could be multiple revisions of a report, the qry is set to return unique results.

The output of this qry is then used to feed 2 text boxes on the form: txtSelectRev and txtSelectID. I use DMax to identify the highest value of Report_Version in txtSelectRev and the ID of this record in txtSelectID, txtSelectRev is used for reference only and the value of txtSelectID is used to populate a report Based on the record selected.

The control source for txtSelectRev is:
Code:
=DMax("[Report_Version]","tbl_Report","[Report_ID] = '" & [cboChooseReport] & "'")

And

txtSelectID:
Code:
=DLookUp("[ID]","tbl_Report","Report_Version = " & [txtSelectRev] & " AND Report_ID = '" & [cboChooseReport] & "'")

As I said, this was working before, but, having changed Report_ID to a number field returns #error for rev and #Type! for ID. If I revert to Field type for Report_ID as text, all is as it should be, but this stops other parts of the project working

I have tried to change syntax, but I am out of my depth.

Could anyone help please?
 

Attachments

  • Select.zip
    36.8 KB · Views: 145
Last edited:

bob fitz

AWF VIP
Local time
Today, 14:21
Joined
May 23, 2011
Messages
4,717
Not sure if this is what you want but I have removed the apostrophes ( ' ) from the expressions as they are only needed for text fields and not for numeric fields. Hope this helps.
 

Attachments

  • SelectBob01.zip
    39 KB · Views: 146

Manicduck

New member
Local time
Today, 14:21
Joined
May 22, 2020
Messages
29
@bob fitz, thank you so much! I can’t tell you how many cups of coffee I have drunk trying to sort this out!

I am very grateful
 

bob fitz

AWF VIP
Local time
Today, 14:21
Joined
May 23, 2011
Messages
4,717
@bob fitz, thank you so much! I can’t tell you how many cups of coffee I have drunk trying to sort this out!

I am very grateful
Unfortunately, these are the kind of problems that arise when we make changes to tables. Glad it was what you needed and pleased to hear that it was only coffee that you turned to :eek: :unsure:
 

Manicduck

New member
Local time
Today, 14:21
Joined
May 22, 2020
Messages
29
Yes the knock-on effects of a seemingly small change!

I cut and pasted your code changes for this and the sequential number help you offered yesterday next to my code in a word doc and was able to work out the differences and similarities between the two solutions . This helped me to correct a few more errors which had crept in as a result of this change.

once again, thank you for your efforts on my behalf
 

bob fitz

AWF VIP
Local time
Today, 14:21
Joined
May 23, 2011
Messages
4,717
Yes the knock-on effects of a seemingly small change!

I cut and pasted your code changes for this and the sequential number help you offered yesterday next to my code in a word doc and was able to work out the differences and similarities between the two solutions . This helped me to correct a few more errors which had crept in as a result of this change.

once again, thank you for your efforts on my behalf
You're Welcome :) and I'm glad that you were able to correct other similar faults. That shows that you have learnt a little more. We are all striving to achieve that. Well done!
 

Users who are viewing this thread

Top Bottom