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@xxxxxxxxxxx via AccessMonster.com" <u15580@uwe> wrote in message
news:5f9bdf9ae30ad@xxxxxx
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/Forums.aspx/databases-ms-access/200605/1


.

Relevant Pages

  • Re: XL-2003 - Class Module - Controls declared "withEvents" dont sink events..
    ... To answer your question, yes there will be more buttons, but there will also be other related controls used in the same class. ... My understanding is that the collection you suggest keeps the objBtn object in scope once the Initialize routine finishes. ... Private Sub UserForm_Initialize ... Dim objBtn As clsBtn ...
    (microsoft.public.excel.programming)
  • Re: go to a record in a form
    ... together will automatically fill in an invisible txtboxdate that is bound to ... Private Sub isFindDups() ... *unbound* controls, that simplifies things considerably. ...
    (comp.databases.ms-access)
  • Re: XL-2003 - Class Module - Controls declared "withEvents" dont sink events..
    ... Private Sub UserForm_Initialize ... you dont have to "twist" you controls names to fit a given pattern ... The user can enter a date in the textbox, use arrow keys to change days, click on the button to pop the calendar control as he wish. ... But as I said before, if you don't want a control array, what is the purpose of the event sink class? ...
    (microsoft.public.excel.programming)
  • Re: Modeless userform OLEObject Add problem
    ... makes no difference running from an ActiveX button ... However no problem to add controls to any sheet EXCEPT ... > Private Sub UserForm_Activate ...
    (microsoft.public.excel.programming)
  • Re: Form Field Validation On The Fly
    ... Allen Browne - Microsoft MVP. ... You cannot use the events of the controls, ... >> Private Sub Form_BeforeUpdate ... >> only validation I require is that they are not empty. ...
    (microsoft.public.access.formscoding)