query problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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.

Klatuu said:
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


Chuck said:
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
Click to expand...
Click to expand...
 
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


Chuck said:
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.

Klatuu said:
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


Chuck said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


Chuck said:
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.

Klatuu said:
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
Click to expand...
Click to expand...
Click to expand...
 
Glad you got it working, but I still don't know why it did not error out
before.
--
Dave Hargis, Microsoft Access MVP


Chuck said:
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


Chuck said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top