Make a VBA Code shorter or more than one line

[Khalil Handal]

Hi to All,

I am trying to have this VBA one line of code to be shorter or to put it in more than one line.

ChkoutCopies = DCount("[fkBookId]", "tblBorrowedBooks", "[fkBookId]= Forms!frmBorrowingBooks!cntBorrowedBooksSubForm.Form!txtfkBookId AND DateReturned Is Null AND DateBorrowed Is Not Null")

It seems I am missing something in my trials.
Khalil

[CJ_London]

Where is the code in relation to your borrowingbooks form?

also investigate the use of the vba continuation character (the underscore)

[Micron]

That looks like it might be a calculated field in a query and not VBA. Is Null is for sql, IsNull() is for VBA?

[June7]

IS NULL works in domain aggregate function WHERE argument, no matter where the function is used. The IS NULL is within quotes and processed by the WHERE argument which follows SQL syntax.

Note use of continuation character (_) and & use when breaking a string. Ampersand could be at beginning of following line if you prefer. Be sure not to lose spaces when breaking strings.

ChkoutCopies = DCount("*", "tblBorrowedBooks", _
                        "[fkBookId]= Forms!frmBorrowingBooks!cntBorrowedBooksSubForm.Form!txtfkBookId " & _
                                            "AND DateReturned Is Null AND DateBorrowed Is Not Null")

If code is behind subform, instead of embedding full control path, could concatenate.

ChkoutCopies = DCount("*", "tblBorrowedBooks", _
                       "[fkBookId]= " & Me.txtfkBookId  & " AND DateReturned Is Null AND DateBorrowed Is Not Null")

[Welshgasman]

I always put the space at the start of the next line

Easier to spot, instead of having to scroll to the far right to make sure one is there on the previous line.

[June7]

Good point. Ideally, lines are short enough to not need scrolling but that does not always happen, especially if VBA window width is changed during coding/debugging.

I do wonder why saving an aggregate value to a field/control with VBA.

[Khalil Handal]

Hi to all,

The text box txtfkBookId is on the subform. The code is also behind an AfterUpdate event of a Date field on the subform. I will be using the: & Me.txtfkBookId.

The ChkoutCopies is to know how many books are chekced out and to see if we still have copies available; If we still have copies the user can borrow one, else it shows a message that no copies are available and the user has to reserve the book until it is returned to the library.

Thank you all for the help.
Solved

Khalil

[June7]

My point is, probably don't need to save this value to table. Just calculate when needed. Domain aggregate expression can be in textbox ControlSource. Regardless, record would have to first be committed to table if you want it to be included in the aggregate value.