Code Problem

[LMRollins]

I am using the following code which is working fine but I need something added to it but I can't seem to get it to work.

Private Sub txtPage_Ln_AfterUpdate()
If DLookup("Page_Ln", "tblPhysicalEnter", "Page_Ln = '" & txtPage_Ln & "'&quot Then
MsgBox "Warning - Page & line number already exists", vbInformation
Me.txtPageNo.SetFocus
Me.txtPage_Ln.SetFocus
SendKeys ("End&quot
End If
End Sub

I want it to also check for null values and give a message box. I have also broken down the page_ln into two unbound fields - pageno and lineno. I also need something included that will check the pageno and let me if it's greater than 600 and the lineno is greater than 26.

 

[rorymurray]

What happens when you try this:

If DLookup("Page_Ln", "tblPhysicalEnter", "Page_Ln is null&quot Then....

 

[Guest_imported]

Hi

Check that Page_Ln is numeric (ie is defined in table structure as Number). If it is defined as text try :


Private Sub txtPage_Ln_AfterUpdate()
If DLookup("Page_Ln", "tblPhysicalEnter", "Page_Ln = " & chr(34) & Trim(txtPage_Ln) & chr(34)) Then
MsgBox "Warning - Page & line number already exists", vbInformation
Me.txtPageNo.SetFocus
Me.txtPage_Ln.SetFocus
SendKeys ("End&quot
End If
End Sub


Regards,
Mr Big

 

[LMRollins]

I don't think I explained very well. The code I have works fine but I need something combined with it to do the following:

If the field is null then msgbox. If duplicate value already in table then msgbox (which I already have). If the ltrim(page_ln,3) is greater than 600 then msgbox. If the rtrim(page_ln,2) is greater than 26 then msgbox.

I'm just learning code and don't quite know how to combine all this together to work. It might not even be possible but I don't know enough about coding to know this either.

 

[Guest_imported]

Hi.

I think you need CInt(ltrim(page_ln,3)) this will convert from string to integer. If that doesn't work, look at the "Val" keyword.

Hope this helps, please let me know.

Regards,
Mr Big.

 

[LMRollins]

I'm sorry that I'm not code literate but to make all four of these options work do I need a if then else statement and do they need to be in a certain order?

 

[jjob]

I think this might do, there is an implied priority , ie. null found first, then the values then the duplicates

I can't remember if Val() assumes that the text is numeric and can be converted, or maybe it's Cint(), but you can test for non-numeric and see what happens. If you want belt and braces code you would probably check for isNumber(pageno) before using val




Private Sub txtPage_Ln_AfterUpdate()


if isnull(txtPage_Ln) then
msgbox "is null"
elseif (val(pageno) > 600 ) and (val(lnno) > 26) then
msgbox "over the required figures."
elseIf DLookup("Page_Ln", "tblPhysicalEnter", "Page_Ln = '" & txtPage_Ln & "'&quot Then
MsgBox "Warning - Page & line number already exists", vbInformation
Me.txtPageNo.SetFocus
Me.txtPage_Ln.SetFocus
SendKeys ("End&quot
End If
End Sub



regards

John

 

[LMRollins]

jjob, that works fine but only some of the time. All of a sudden the codes that I have applied don't work anymore. Do you know what could be causing this?