go to a record in a form

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

re: go to a record in a form

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


"kaosyeti@comcast.net via AccessMonster.com" <u15580@uwe> wrote in message
news:5f7fda090a0ae@uwe...[color=blue]
> 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[/color]
that[color=blue]
> already has a record created, she/he won't know it until access tries to[/color]
save[color=blue]
> 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[/color]
in,[color=blue]
> 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[/color]
form[color=blue]
> 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[/color]
the[color=blue]
> duplicate issue first. thanks.
>
> --
> Greg
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...ccess/200604/1[/color]

re: go to a record in a form

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:[color=blue]
>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
>[color=green]
>> 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[/color]
>[quoted text clipped - 12 lines][color=green]
>> duplicate record and it won't go to another record without taking care of the
>> duplicate issue first. thanks.[/color][/color]

--
Greg

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

re: go to a record in a form

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


"kaosyeti@comcast.net via AccessMonster.com" <u15580@uwe> wrote in message
news:5f9bdf9ae30ad@uwe...[color=blue]
> 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,[/color]
that's[color=blue]
> when your code executes and i get the error. can you see where my[/color]
disconnect[color=blue]
> is?
>
> tina wrote:[color=green]
> >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[/color][/color]
from[color=blue][color=green]
> >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[/color][/color]
above[color=blue][color=green]
> >code assumes that the "year/month field" in the table is a Text data[/color][/color]
type,[color=blue][color=green]
> >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[/color][/color]
invisible[color=blue][color=green]
> >control is set in the *current* record (presumably a new record).
> >
> >hth
> >[color=darkred]
> >> hey.... i have a duplicate record issue i could use some help with. on[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> >> form that has 2 unbound controls, txtboxyear and cboxmonth, together[/color][/color][/color]
will[color=blue][color=green]
> >[quoted text clipped - 12 lines][color=darkred]
> >> duplicate record and it won't go to another record without taking care[/color][/color][/color]
of the[color=blue][color=green][color=darkred]
> >> duplicate issue first. thanks.[/color][/color]
>
> --
> Greg
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...ccess/200605/1[/color]

re: go to a record in a form

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:[color=blue]
>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
>[color=green]
>> tina... it's not working quite yet. this is what i have:
>>[/color]
>[quoted text clipped - 70 lines][color=green][color=darkred]
>> >> duplicate record and it won't go to another record without taking care of the
>> >> duplicate issue first. thanks.[/color][/color][/color]

--
Greg

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

re: go to a record in a form

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:[color=blue]
>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
>[color=green]
>> tina... it's not working quite yet. this is what i have:
>>[/color]
>[quoted text clipped - 70 lines][color=green][color=darkred]
>> >> duplicate record and it won't go to another record without taking care of the
>> >> duplicate issue first. thanks.[/color][/color][/color]

--
Greg

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

re: go to a record in a form

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


"kaosyeti@comcast.net via AccessMonster.com" <u15580@uwe> wrote in message
news:5fa7058263a26@uwe...[color=blue]
> tina.... truthfully, i thought that might be the case -- it needed a
> conditional statement before the findfirst line of code -- but i've only[/color]
been[color=blue]
> doing this since september and have never coded before so i didn't know.[/color]
but[color=blue]
> it did play with it for a while that way before my reply to tell you that[/color]
it[color=blue]
> wasn't working, to no avail. i followed your code which is a little[/color]
cleaner[color=blue]
> 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[/color]
fix[color=blue]
> 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:[color=green]
> >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[/color][/color]
corrected[color=blue][color=green]
> >code. follow my directions again, and see if it'll work for you this[/color][/color]
time.[color=blue][color=green]
> >
> >***********
> >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[/color][/color]
from[color=blue][color=green]
> >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[/color][/color]
above[color=blue][color=green]
> >code assumes that the "year/month field" in the table is a Text data[/color][/color]
type,[color=blue][color=green]
> >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[/color][/color]
invisible[color=blue][color=green]
> >control is set in the *current* record (presumably a new record).
> >
> >*********
> >hth
> >[color=darkred]
> >> tina... it's not working quite yet. this is what i have:
> >>[/color]
> >[quoted text clipped - 70 lines][color=darkred]
> >> >> duplicate record and it won't go to another record without taking[/color][/color][/color]
care of the[color=blue][color=green][color=darkred]
> >> >> duplicate issue first. thanks.[/color][/color]
>
> --
> Greg
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...ccess/200605/1[/color]