go to a record in a form

[kaosyeti]

hey.... i have a duplicate record issue i could use some help with. on a
form that has 2 unbound controls, txtboxyear and cboxmonth, together will
automatically fill in an invisible txtboxdate that is bound to a table and
has no duplicates allowed. the problem is that there are 30 or so other
controls here to be filled in and if the user selects a year and month that
already has a record created, she/he won't know it until access tries to save
the record, then it will tell them that that record already exists.

how can i set things up so that when txtboxyear and cboxmonth are filled in,
if there's a record with that date already, it just goes to that record
automatically?

the problem i ran into when i tried findrecord is that it won't let the form
display another record because the month and year controls together form a
duplicate record and it won't go to another record without taking care of the
duplicate issue first. thanks.

--
Greg

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200604/1

[tina]

well, since you're starting the data entry by entering values in two
*unbound* controls, that simplifies things considerably. try adding the
following procedure to the form's module, and then call the procedure from
the AfterUpdate event of *both* of the unbound controls.

Private Sub isFindDups()

Me.Recordset.FindFirst "YearMonthField = '" _
Me!txtboxyear & Me!cobxmonth & "'"
If Me.Recordset.NoMatch Then
' put here the code to fill in the invisible txtboxdate
' control in the form.
End If

End Sub

substitute the correct control and field names, of course. also, the above
code assumes that the "year/month field" in the table is a Text data type,
and that the value is built as "year first, then month, with no spaces in
the text". adjust the code as necessary, if your setup is different.

if both the year and month controls on the form have values entered, the
code searches the form's Recordset for a matching record. if a match is
found, the focus moves to that record, otherwise the value of the invisible
control is set in the *current* record (presumably a new record).

hth
"ka******@comcast.net via AccessMonster.com" <u15580@uwe> wrote in message
news:5f7fda090a0ae@uwe...

hey.... i have a duplicate record issue i could use some help with. on a
form that has 2 unbound controls, txtboxyear and cboxmonth, together will
automatically fill in an invisible txtboxdate that is bound to a table and
has no duplicates allowed. the problem is that there are 30 or so other
controls here to be filled in and if the user selects a year and month that already has a record created, she/he won't know it until access tries to save the record, then it will tell them that that record already exists.

how can i set things up so that when txtboxyear and cboxmonth are filled in, if there's a record with that date already, it just goes to that record
automatically?

the problem i ran into when i tried findrecord is that it won't let the form display another record because the month and year controls together form a
duplicate record and it won't go to another record without taking care of the duplicate issue first. thanks.

--
Greg

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200604/1

[kaosyeti]

tina... it's not working quite yet. this is what i have:

Private Sub isFindDups()
Dim strwhere
Me.Recordset.FindFirst "date = #" & Me.txtboxdate & "#"
If Me.Recordset.NoMatch Then
If IsNull(Me.txtboxyear) = False Then
strwhere = CDate(Me.cboxmonth & " 1, " & Me.txtboxyear)
End If
If IsNull(Me.cboxmonth) = False Then
strwhere = CDate(Me.cboxmonth & " 1, " & Me.txtboxyear)
End If
Me.txtboxdate = strwhere
End If
End Sub

and

Private Sub cboxmonth_AfterUpdate()
If IsNull(Me.txtboxyear) = False Then
Call isFindDups
End If
End Sub

and

Private Sub txtboxyear_AfterUpdate()
Dim strdate As Date

If IsNull(Me.cboxmonth) = False Then
Call isFindDups
End If
End Sub

and the message it gives me is that this action has been cancelled by an
associated event. the debugger opens on the first line of isFindDups().
also, when i enter the year and month, txtboxdate DOES fill itself in,
however nothing happens. then if i change either the year or month, that's
when your code executes and i get the error. can you see where my disconnect
is?

tina wrote:

well, since you're starting the data entry by entering values in two
*unbound* controls, that simplifies things considerably. try adding the
following procedure to the form's module, and then call the procedure from
the AfterUpdate event of *both* of the unbound controls.

Private Sub isFindDups()

Me.Recordset.FindFirst "YearMonthField = '" _
Me!txtboxyear & Me!cobxmonth & "'"
If Me.Recordset.NoMatch Then
' put here the code to fill in the invisible txtboxdate
' control in the form.
End If

End Sub

substitute the correct control and field names, of course. also, the above
code assumes that the "year/month field" in the table is a Text data type,
and that the value is built as "year first, then month, with no spaces in
the text". adjust the code as necessary, if your setup is different.

if both the year and month controls on the form have values entered, the
code searches the form's Recordset for a matching record. if a match is
found, the focus moves to that record, otherwise the value of the invisible
control is set in the *current* record (presumably a new record).

hth

hey.... i have a duplicate record issue i could use some help with. on a
form that has 2 unbound controls, txtboxyear and cboxmonth, together will

[quoted text clipped - 12 lines]

duplicate record and it won't go to another record without taking care of the
duplicate issue first. thanks.

--
Greg

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200605/1

[tina]

kao, i'm really sorry - i posted the code *without* the "controlling" If
statement. hate to say this, but try deleting the code you wrote, and
starting over. below is a re-post of my initial response BUT with corrected
code. follow my directions again, and see if it'll work for you this time.

***********
well, since you're starting the data entry by entering values in two
*unbound* controls, that simplifies things considerably. try adding the
following procedure to the form's module, and then call the procedure from
the AfterUpdate event of *both* of the unbound controls.

Private Sub isFindDups()

If Not IsNull(Me!txtboxyear) And _
Not IsNull(Me!cboxmonth) Then
Me.Recordset.FindFirst "YearMonthField = '" _
Me!txtboxyear & Me!cboxmonth & "'"
If Me.Recordset.NoMatch Then
' put here the code to fill in the invisible txtboxdate
' control in the form.
End If
End If

End Sub

substitute the correct control and field names, of course. also, the above
code assumes that the "year/month field" in the table is a Text data type,
and that the value is built as "year first, then month, with no spaces in
the text". adjust the code as necessary, if your setup is different.

if both the year and month controls on the form have values entered, the
code searches the form's Recordset for a matching record. if a match is
found, the focus moves to that record. otherwise, the value of the invisible
control is set in the *current* record (presumably a new record).

*********
hth
"ka******@comcast.net via AccessMonster.com" <u15580@uwe> wrote in message
news:5f9bdf9ae30ad@uwe...

tina... it's not working quite yet. this is what i have:

Private Sub isFindDups()
Dim strwhere
Me.Recordset.FindFirst "date = #" & Me.txtboxdate & "#"
If Me.Recordset.NoMatch Then
If IsNull(Me.txtboxyear) = False Then
strwhere = CDate(Me.cboxmonth & " 1, " & Me.txtboxyear)
End If
If IsNull(Me.cboxmonth) = False Then
strwhere = CDate(Me.cboxmonth & " 1, " & Me.txtboxyear)
End If
Me.txtboxdate = strwhere
End If
End Sub

and

Private Sub cboxmonth_AfterUpdate()
If IsNull(Me.txtboxyear) = False Then
Call isFindDups
End If
End Sub

and

Private Sub txtboxyear_AfterUpdate()
Dim strdate As Date

If IsNull(Me.cboxmonth) = False Then
Call isFindDups
End If
End Sub

and the message it gives me is that this action has been cancelled by an
associated event. the debugger opens on the first line of isFindDups().
also, when i enter the year and month, txtboxdate DOES fill itself in,
however nothing happens. then if i change either the year or month, that's when your code executes and i get the error. can you see where my disconnect is?

tina wrote:

well, since you're starting the data entry by entering values in two
*unbound* controls, that simplifies things considerably. try adding the
following procedure to the form's module, and then call the procedure fromthe AfterUpdate event of *both* of the unbound controls.

Private Sub isFindDups()

Me.Recordset.FindFirst "YearMonthField = '" _
Me!txtboxyear & Me!cobxmonth & "'"
If Me.Recordset.NoMatch Then
' put here the code to fill in the invisible txtboxdate
' control in the form.
End If

End Sub

substitute the correct control and field names, of course. also, the abovecode assumes that the "year/month field" in the table is a Text data type,and that the value is built as "year first, then month, with no spaces in
the text". adjust the code as necessary, if your setup is different.

if both the year and month controls on the form have values entered, the
code searches the form's Recordset for a matching record. if a match is
found, the focus moves to that record, otherwise the value of the invisiblecontrol is set in the *current* record (presumably a new record).

hth

hey.... i have a duplicate record issue i could use some help with. on a form that has 2 unbound controls, txtboxyear and cboxmonth, together will

[quoted text clipped - 12 lines]

duplicate record and it won't go to another record without taking care

of the duplicate issue first. thanks.

--
Greg

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200605/1

[kaosyeti]

tina.... truthfully, i thought that might be the case -- it needed a
conditional statement before the findfirst line of code -- but i've only been
doing this since september and have never coded before so i didn't know. but
it did play with it for a while that way before my reply to tell you that it
wasn't working, to no avail. i followed your code which is a little cleaner
than what i had and i got the exact same error message and the exact same
issue with it not executing the code on the first go around but only after
you change either the month or year a 2nd time. sorry, but this didn't fix
the issue.

Private Sub isFindDups()
Dim strwhere
If Not IsNull(Me.txtboxyear) And Not IsNull(Me.cboxmonth) Then
Me.Recordset.FindFirst "date = #" & Me.txtboxdate & "#"
If Me.Recordset.NoMatch Then
If IsNull(Me.txtboxyear) = False Then
strwhere = CDate(Me.cboxmonth & " 1, " & Me.txtboxyear)
End If
If IsNull(Me.cboxmonth) = False Then
strwhere = CDate(Me.cboxmonth & " 1, " & Me.txtboxyear)
End If
Me.txtboxdate = strwhere
End If
End If
End Sub

tina wrote:

kao, i'm really sorry - i posted the code *without* the "controlling" If
statement. hate to say this, but try deleting the code you wrote, and
starting over. below is a re-post of my initial response BUT with corrected
code. follow my directions again, and see if it'll work for you this time.

***********
well, since you're starting the data entry by entering values in two
*unbound* controls, that simplifies things considerably. try adding the
following procedure to the form's module, and then call the procedure from
the AfterUpdate event of *both* of the unbound controls.

Private Sub isFindDups()

If Not IsNull(Me!txtboxyear) And _
Not IsNull(Me!cboxmonth) Then
Me.Recordset.FindFirst "YearMonthField = '" _
Me!txtboxyear & Me!cboxmonth & "'"
If Me.Recordset.NoMatch Then
' put here the code to fill in the invisible txtboxdate
' control in the form.
End If
End If

End Sub

substitute the correct control and field names, of course. also, the above
code assumes that the "year/month field" in the table is a Text data type,
and that the value is built as "year first, then month, with no spaces in
the text". adjust the code as necessary, if your setup is different.

if both the year and month controls on the form have values entered, the
code searches the form's Recordset for a matching record. if a match is
found, the focus moves to that record. otherwise, the value of the invisible
control is set in the *current* record (presumably a new record).

*********
hth

tina... it's not working quite yet. this is what i have:

[quoted text clipped - 70 lines]

>> duplicate record and it won't go to another record without taking care of the
>> duplicate issue first. thanks.

--
Greg

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200605/1

[kaosyeti]

hey.. i've been playing more and here's the newest. i tried this on a
different form that only has the year as the bound control, both in the
before and after update events. i get the same result each time -- the
action was cancelled by an associated event. i hope this new info helps.

Private Sub txtboxyear_AfterUpdate()
Dim strwhere
strwhere = Me.txtboxyear
Me.Recordset.Findfirst "year = " & strwhere
If Me.Recordset.NoMatch Then
Me.txtboxyear = strwhere
End If
End Sub

OR

Private Sub txtboxyear_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[year]", "tblusedforecast", "[year] = " & Me.
txtboxyear)) Then
Me.Recordset.findfirst "year = " & Me.txtboxyear
End If
End Sub

tina wrote:

kao, i'm really sorry - i posted the code *without* the "controlling" If
statement. hate to say this, but try deleting the code you wrote, and
starting over. below is a re-post of my initial response BUT with corrected
code. follow my directions again, and see if it'll work for you this time.

***********
well, since you're starting the data entry by entering values in two
*unbound* controls, that simplifies things considerably. try adding the
following procedure to the form's module, and then call the procedure from
the AfterUpdate event of *both* of the unbound controls.

Private Sub isFindDups()

If Not IsNull(Me!txtboxyear) And _
Not IsNull(Me!cboxmonth) Then
Me.Recordset.FindFirst "YearMonthField = '" _
Me!txtboxyear & Me!cboxmonth & "'"
If Me.Recordset.NoMatch Then
' put here the code to fill in the invisible txtboxdate
' control in the form.
End If
End If

End Sub

substitute the correct control and field names, of course. also, the above
code assumes that the "year/month field" in the table is a Text data type,
and that the value is built as "year first, then month, with no spaces in
the text". adjust the code as necessary, if your setup is different.

if both the year and month controls on the form have values entered, the
code searches the form's Recordset for a matching record. if a match is
found, the focus moves to that record. otherwise, the value of the invisible
control is set in the *current* record (presumably a new record).

*********
hth

tina... it's not working quite yet. this is what i have:

[quoted text clipped - 70 lines]

>> duplicate record and it won't go to another record without taking care of the
>> duplicate issue first. thanks.

--
Greg

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200605/1

[tina]

okay, i read your second thread (posted several hours after this one), but
let's stick with this one for the moment. i don't think you're following
quite what i'm intending for the code to do - or perhaps i'm not quite
following what you want the code to do. so i've got a few questions for you:

1. your initial post said "2 unbound controls, txtboxyear and cboxmonth,
together will automatically fill in an invisible txtboxdate that is bound to
a table". txtboxdate is bound to a *field* in a table, correct? if the
field's name is "date", you should change it (see
http://home.att.net/~california.db/tips.html#aTip5 for more information).
if the field's name is not "date", then what is it? also , is the field's
data type Date/Time?
2. how are you combining the values in txtboxyear and cboxmonth to get a
single value that you use to populate the invisible date field? i assume
you're doing that with VBA code, so please post the expression you're using.

hth
"ka******@comcast.net via AccessMonster.com" <u15580@uwe> wrote in message
news:5fa7058263a26@uwe...

tina.... truthfully, i thought that might be the case -- it needed a
conditional statement before the findfirst line of code -- but i've only been doing this since september and have never coded before so i didn't know. but it did play with it for a while that way before my reply to tell you that it wasn't working, to no avail. i followed your code which is a little cleaner than what i had and i got the exact same error message and the exact same
issue with it not executing the code on the first go around but only after
you change either the month or year a 2nd time. sorry, but this didn't fix the issue.

Private Sub isFindDups()
Dim strwhere
If Not IsNull(Me.txtboxyear) And Not IsNull(Me.cboxmonth) Then
Me.Recordset.FindFirst "date = #" & Me.txtboxdate & "#"
If Me.Recordset.NoMatch Then
If IsNull(Me.txtboxyear) = False Then
strwhere = CDate(Me.cboxmonth & " 1, " & Me.txtboxyear)
End If
If IsNull(Me.cboxmonth) = False Then
strwhere = CDate(Me.cboxmonth & " 1, " & Me.txtboxyear)
End If
Me.txtboxdate = strwhere
End If
End If
End Sub

tina wrote:

kao, i'm really sorry - i posted the code *without* the "controlling" If
statement. hate to say this, but try deleting the code you wrote, and
starting over. below is a re-post of my initial response BUT with correctedcode. follow my directions again, and see if it'll work for you this time.
***********
well, since you're starting the data entry by entering values in two
*unbound* controls, that simplifies things considerably. try adding the
following procedure to the form's module, and then call the procedure fromthe AfterUpdate event of *both* of the unbound controls.

Private Sub isFindDups()

If Not IsNull(Me!txtboxyear) And _
Not IsNull(Me!cboxmonth) Then
Me.Recordset.FindFirst "YearMonthField = '" _
Me!txtboxyear & Me!cboxmonth & "'"
If Me.Recordset.NoMatch Then
' put here the code to fill in the invisible txtboxdate
' control in the form.
End If
End If

End Sub

substitute the correct control and field names, of course. also, the abovecode assumes that the "year/month field" in the table is a Text data type,and that the value is built as "year first, then month, with no spaces in
the text". adjust the code as necessary, if your setup is different.

if both the year and month controls on the form have values entered, the
code searches the form's Recordset for a matching record. if a match is
found, the focus moves to that record. otherwise, the value of the invisiblecontrol is set in the *current* record (presumably a new record).

*********
hth

tina... it's not working quite yet. this is what i have:

[quoted text clipped - 70 lines]

>> duplicate record and it won't go to another record without taking care of the >> duplicate issue first. thanks.

--
Greg

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200605/1