query problem

[Guest]

I had a form (with Tab1 control) based on a table that incremented the
Home_Addr_PK field and everything worked well. Recently i changed the form to
be based on a query as i had to add Tab2. Tab2 contains the same control
tblAddress_Home_Addr_PK. When i fire the New_Home_Address button, the value
is always "HA0001" and not the correct value. What do i need to change.

Private Sub New_Home_Address_Click()
Dim strAddr As String
Dim intAddr As Integer
On Error GoTo Err_New_Home_Address_Click

DoCmd.GoToRecord , , acNewRec
strAddr = NZ(DMax("[tblAddress_Home_Addr_PK]", "tblAddress"). "HA0000")
intAddr = Val(Mid(strAddr, 3) ' extract numeric portion
If intAddr < 9999 Then
Me![tblAddress_Home_Addr_PK = Left(strAddr, 2) & Format(intAddr + 1, "0000")
Else
MsgBox "Turn off the computer - out of address numbers", vbOKOnly
End If
Exit_New_Home_Address_Click:
Exit Sub

Err_New_Home_Address_Click:
MsgBox Err.Description
Resume Exit_New_Home_Address_Click
End Sub

 

[Guest]

strAddr = NZ(DMax("[tblAddress_Home_Addr_PK]", "tblAddress"). "HA0000")

^
Is the character before "HA0000" a comma or a period? It looks like a
period and should be a comma.

 

[Guest]

I will check this evening, this was lifted from another thread so it might be
a typo - i think it is a comma as there is no error produced from the VBA and
it would be in error if it is a period (.)
Just to clarify there is no field in tblAddress called
"tblAddres_Home_Addr_PK", the field name is Home_Addr_PK but i thought i had
to reference the control from the query. Maybe i have this wrong.

strAddr = NZ(DMax("[tblAddress_Home_Addr_PK]", "tblAddress"). "HA0000")

^
Is the character before "HA0000" a comma or a period? It looks like a
period and should be a comma.
--
Dave Hargis, Microsoft Access MVP

I had a form (with Tab1 control) based on a table that incremented the
Home_Addr_PK field and everything worked well. Recently i changed the form to
be based on a query as i had to add Tab2. Tab2 contains the same control
tblAddress_Home_Addr_PK. When i fire the New_Home_Address button, the value
is always "HA0001" and not the correct value. What do i need to change.

Private Sub New_Home_Address_Click()
Dim strAddr As String
Dim intAddr As Integer
On Error GoTo Err_New_Home_Address_Click

DoCmd.GoToRecord , , acNewRec
strAddr = NZ(DMax("[tblAddress_Home_Addr_PK]", "tblAddress"). "HA0000")
intAddr = Val(Mid(strAddr, 3) ' extract numeric portion
If intAddr < 9999 Then
Me![tblAddress_Home_Addr_PK = Left(strAddr, 2) & Format(intAddr + 1, "0000")
Else
MsgBox "Turn off the computer - out of address numbers", vbOKOnly
End If
Exit_New_Home_Address_Click:
Exit Sub

Err_New_Home_Address_Click:
MsgBox Err.Description
Resume Exit_New_Home_Address_Click
End Sub

 

[Guest]

I thought there should be an error it were a period, but I could not find
anything else until your last post. You do not have to reference the domain
name in a DMax. The second argument does that. All you need is
strAddr = NZ(DMax("[Home_Addr_PK]", "tblAddress"). "HA0000")

Now, what is even stranger, is you should actually get an error 2001 when
you try to execute it as it is.
--
Dave Hargis, Microsoft Access MVP

I will check this evening, this was lifted from another thread so it might be
a typo - i think it is a comma as there is no error produced from the VBA and
it would be in error if it is a period (.)
Just to clarify there is no field in tblAddress called
"tblAddres_Home_Addr_PK", the field name is Home_Addr_PK but i thought i had
to reference the control from the query. Maybe i have this wrong.

strAddr = NZ(DMax("[tblAddress_Home_Addr_PK]", "tblAddress"). "HA0000")

^
Is the character before "HA0000" a comma or a period? It looks like a
period and should be a comma.
--
Dave Hargis, Microsoft Access MVP

I had a form (with Tab1 control) based on a table that incremented the
Home_Addr_PK field and everything worked well. Recently i changed the form to
be based on a query as i had to add Tab2. Tab2 contains the same control
tblAddress_Home_Addr_PK. When i fire the New_Home_Address button, the value
is always "HA0001" and not the correct value. What do i need to change.

Private Sub New_Home_Address_Click()
Dim strAddr As String
Dim intAddr As Integer
On Error GoTo Err_New_Home_Address_Click

DoCmd.GoToRecord , , acNewRec
strAddr = NZ(DMax("[tblAddress_Home_Addr_PK]", "tblAddress"). "HA0000")
intAddr = Val(Mid(strAddr, 3) ' extract numeric portion
If intAddr < 9999 Then
Me![tblAddress_Home_Addr_PK = Left(strAddr, 2) & Format(intAddr + 1, "0000")
Else
MsgBox "Turn off the computer - out of address numbers", vbOKOnly
End If
Exit_New_Home_Address_Click:
Exit Sub

Err_New_Home_Address_Click:
MsgBox Err.Description
Resume Exit_New_Home_Address_Click
End Sub

 

[Guest]

It is in fact a comma (,) and i changed the field back to what you have below
and its working again.
thanks

I thought there should be an error it were a period, but I could not find
anything else until your last post. You do not have to reference the domain
name in a DMax. The second argument does that. All you need is
strAddr = NZ(DMax("[Home_Addr_PK]", "tblAddress"). "HA0000")

Now, what is even stranger, is you should actually get an error 2001 when
you try to execute it as it is.
--
Dave Hargis, Microsoft Access MVP

I will check this evening, this was lifted from another thread so it might be
a typo - i think it is a comma as there is no error produced from the VBA and
it would be in error if it is a period (.)
Just to clarify there is no field in tblAddress called
"tblAddres_Home_Addr_PK", the field name is Home_Addr_PK but i thought i had
to reference the control from the query. Maybe i have this wrong.

strAddr = NZ(DMax("[tblAddress_Home_Addr_PK]", "tblAddress"). "HA0000")

^
Is the character before "HA0000" a comma or a period? It looks like a
period and should be a comma.
--
Dave Hargis, Microsoft Access MVP


:

I had a form (with Tab1 control) based on a table that incremented the
Home_Addr_PK field and everything worked well. Recently i changed the form to
be based on a query as i had to add Tab2. Tab2 contains the same control
tblAddress_Home_Addr_PK. When i fire the New_Home_Address button, the value
is always "HA0001" and not the correct value. What do i need to change.

Private Sub New_Home_Address_Click()
Dim strAddr As String
Dim intAddr As Integer
On Error GoTo Err_New_Home_Address_Click

DoCmd.GoToRecord , , acNewRec
strAddr = NZ(DMax("[tblAddress_Home_Addr_PK]", "tblAddress"). "HA0000")
intAddr = Val(Mid(strAddr, 3) ' extract numeric portion
If intAddr < 9999 Then
Me![tblAddress_Home_Addr_PK = Left(strAddr, 2) & Format(intAddr + 1, "0000")
Else
MsgBox "Turn off the computer - out of address numbers", vbOKOnly
End If
Exit_New_Home_Address_Click:
Exit Sub

Err_New_Home_Address_Click:
MsgBox Err.Description
Resume Exit_New_Home_Address_Click
End Sub

 

[Guest]

Glad you got it working, but I still don't know why it did not error out
before.
--
Dave Hargis, Microsoft Access MVP

It is in fact a comma (,) and i changed the field back to what you have below
and its working again.
thanks

I thought there should be an error it were a period, but I could not find
anything else until your last post. You do not have to reference the domain
name in a DMax. The second argument does that. All you need is
strAddr = NZ(DMax("[Home_Addr_PK]", "tblAddress"). "HA0000")

Now, what is even stranger, is you should actually get an error 2001 when
you try to execute it as it is.
--
Dave Hargis, Microsoft Access MVP

I will check this evening, this was lifted from another thread so it might be
a typo - i think it is a comma as there is no error produced from the VBA and
it would be in error if it is a period (.)
Just to clarify there is no field in tblAddress called
"tblAddres_Home_Addr_PK", the field name is Home_Addr_PK but i thought i had
to reference the control from the query. Maybe i have this wrong.

:

strAddr = NZ(DMax("[tblAddress_Home_Addr_PK]", "tblAddress"). "HA0000")

^
Is the character before "HA0000" a comma or a period? It looks like a
period and should be a comma.
--
Dave Hargis, Microsoft Access MVP


:

I had a form (with Tab1 control) based on a table that incremented the
Home_Addr_PK field and everything worked well. Recently i changed the form to
be based on a query as i had to add Tab2. Tab2 contains the same control
tblAddress_Home_Addr_PK. When i fire the New_Home_Address button, the value
is always "HA0001" and not the correct value. What do i need to change.

Private Sub New_Home_Address_Click()
Dim strAddr As String
Dim intAddr As Integer
On Error GoTo Err_New_Home_Address_Click

DoCmd.GoToRecord , , acNewRec
strAddr = NZ(DMax("[tblAddress_Home_Addr_PK]", "tblAddress"). "HA0000")
intAddr = Val(Mid(strAddr, 3) ' extract numeric portion
If intAddr < 9999 Then
Me![tblAddress_Home_Addr_PK = Left(strAddr, 2) & Format(intAddr + 1, "0000")
Else
MsgBox "Turn off the computer - out of address numbers", vbOKOnly
End If
Exit_New_Home_Address_Click:
Exit Sub

Err_New_Home_Address_Click:
MsgBox Err.Description
Resume Exit_New_Home_Address_Click
End Sub