microsoft.public.access.forms

"DoveArrow" wrote in message
news:1193088490.153189.172910@v29g2000prd.googlegroups.com...
> On Oct 22, 6:42 am, "Carl Rapson"
> wrote:
>> "DoveArrow" wrote in message
>>
>> news:1192806783.175283.311750@e9g2000prf.googlegroups.com...
>>
>>
>>
>>
>>
>> > On Oct 19, 7:09 am, "Carl Rapson"
>> > wrote:
>> >> "DoveArrow" wrote in message
>>
>> >>news:1192741611.968470.46290@q5g2000prf.googlegroups.com...
>>
>> >> > On Oct 18, 2:05 pm, DoveArrow wrote:
>> >> >> On Oct 18, 2:02 pm, DoveArrow wrote:
>>
>> >> >> > On Oct 18, 8:05 am, "Carl Rapson"
>> >> >> > wrote:
>>
>> >> >> > > "DoveArrow" wrote in message
>>
>> >> >> > >news:1192647377.479074.55450@y27g2000pre.googlegroups.com...
>>
>> >> >> > > > Right now, I have a database with three tables. The first
>> >> >> > > > table
>> >> >> > > > (tblAdvisors) has a list of advisors with some basic
>> >> >> > > > information
>> >> >> > > > about
>> >> >> > > > each one (last name, first name, email address). The second
>> >> >> > > > table
>> >> >> > > > (jtblLocationAdvisor) is a join table where the campuses that
>> >> >> > > > an
>> >> >> > > > advisor advises for are stored (Example: John Smith advises
>> >> >> > > > for
>> >> >> > > > campus
>> >> >> > > > 238 and 241, so his Advisor ID and the Location Numbers he
>> >> >> > > > advises
>> >> >> > > > for
>> >> >> > > > are stored in this table). The third table
>> >> >> > > > (jtblAcademicProgramsByLocationAdvisor) stores the programs
>> >> >> > > > that
>> >> >> > > > an
>> >> >> > > > advisor advises for on a particular campus. Each of these
>> >> >> > > > tables
>> >> >> > > > is
>> >> >> > > > linked together through relationships, so that you can click
>> >> >> > > > on
>> >> >> > > > the
>> >> >> > > > plus sign next to an advisor's name, and then click on the
>> >> >> > > > plus
>> >> >> > > > sign
>> >> >> > > > next to one of the campuses that advisor works on, and then
>> >> >> > > > add
>> >> >> > > > or
>> >> >> > > > delete programs based on campus, like so:
>>
>> >> >> > > > tblAdvisors
>> >> >> > > > |_ jtblLocationAdvisor
>> >> >> > > > ...|_ jtblAcademicProgramsByLocationAdvisor
>>
>> >> >> > > > So that's the basics of how my database works. Now for what
>> >> >> > > > I'm
>> >> >> > > > asking
>> >> >> > > > about.
>>
>> >> >> > > > I'm trying to create a form (frmNewAdvisor) that will 1)
>> >> >> > > > Update
>> >> >> > > > the
>> >> >> > > > tblAdvisors table, and 2) Open a second form that can be used
>> >> >> > > > to
>> >> >> > > > update the jtblLocationAdvisor table. To do this, I created a
>> >> >> > > > fourth
>> >> >> > > > table called tblNewAdvisor and added it as the form's record
>> >> >> > > > source
>> >> >> > > > (Note: I did this, because I didn't want the form to update
>> >> >> > > > tblAdvisors if the user decided to cancel out. I know there
>> >> >> > > > are
>> >> >> > > > better
>> >> >> > > > ways to do this, but as often as I've tried to understand
>> >> >> > > > those
>> >> >> > > > methods, I can't get my head wrapped around them). After
>> >> >> > > > filling
>> >> >> > > > out
>> >> >> > > > this form, I want the person to be able to click a button,
>> >> >> > > > that
>> >> >> > > > will
>> >> >> > > > run an append query (qappCreateNewAdvisor) to append the new
>> >> >> > > > advisor
>> >> >> > > > to tblAdvisors, have it open a second form, and append the
>> >> >> > > > new
>> >> >> > > > advisor's Advisor ID to a text box on that form. To do that,
>> >> >> > > > I've
>> >> >> > > > tried writing the following code:
>>
>> >> >> > > > Private Sub OK_Click()
>> >> >> > > > Dim db As Database
>> >> >> > > > Dim rs As Recordset
>> >> >> > > > Dim strCriteria As String
>>
>> >> >> > > > Set db = CurrentDb
>> >> >> > > > Set rs = db.OpenRecordset("qfltAdvisorLocations",
>> >> >> > > > dbOpenDynaset)
>>
>> >> >> > > > If IsNull(Me.[Last Name]) Then
>> >> >> > > > MsgBox "You have not completed filling out this form."
>> >> >> > > > ElseIf IsNull(Me.[First Name]) Then
>> >> >> > > > MsgBox "You have not completed filling out this form."
>> >> >> > > > ElseIf IsNull(Me.[Email]) Then
>> >> >> > > > MsgBox "You have not completed filling out this form."
>> >> >> > > > Else
>> >> >> > > > Me.Refresh
>> >> >> > > > DoCmd.SetWarnings False
>>
>> >> >> > > > 'Create New Advisor
>> >> >> > > > DoCmd.OpenQuery "qappCreateNewAdvisor"
>>
>> >> >> > > > 'Select Advisor ID for frmAdvisorLocations
>> >> >> > > > On Error GoTo Err_OK_Click
>> >> >> > > > rs.MoveFirst
>> >> >> > > > strCriteria = rs![Advisor ID]
>>
>> >> >> > > > 'Open frmAdvisorLocations
>> >> >> > > > DoCmd.OpenForm "frmAdvisorLocations"
>> >> >> > > > Forms!frmAdvisorLocations![Advisor ID] = strCriteria
>>
>> >> >> > > > 'Close frmCreateAdvisor
>> >> >> > > > DoCmd.Close acForm, "frmCreateAdvisor", acSavePrompt
>> >> >> > > > DoCmd.SetWarnings True
>> >> >> > > > End If
>>
>> >> >> > > > Exit_OK_Click:
>> >> >> > > > Set db = Nothing
>> >> >> > > > Set rs = Nothing
>> >> >> > > > Exit Sub
>>
>> >> >> > > > Err_OK_Click:
>> >> >> > > > MsgBox Err.Description
>> >> >> > > > Resume Exit_OK_Click
>>
>> >> >> > > > End Sub
>>
>> >> >> > > > The problem I'm running into is that when I click on the
>> >> >> > > > button,
>> >> >> > > > I
>> >> >> > > > get
>> >> >> > > > an error message that says "No Current Record." However, if I
>> >> >> > > > click
>> >> >> > > > on
>> >> >> > > > it a second time, it works fine. My guess is it has something
>> >> >> > > > to
>> >> >> > > > do
>> >> >> > > > with the coding I've put together for running the recordset.
>> >> >> > > > I
>> >> >> > > > say
>> >> >> > > > this because I'm still just learning how these work, and I've
>> >> >> > > > made
>> >> >> > > > some pretty silly mistakes with them before, simply because I
>> >> >> > > > didn't
>> >> >> > > > understand what was supposed to happen. Any thoughts?
>>
>> >> >> > > I suspect that you first need to save the current record before
>> >> >> > > running the
>> >> >> > > query. Try adding this to your Click event:
>>
>> >> >> > > If Me.Dirty Then Me.Dirty = False
>> >> >> > > Set db = CurrentDb
>> >> >> > > '...
>>
>> >> >> > > Carl Rapson- Hide quoted text -
>>
>> >> >> > > - Show quoted text -
>>
>> >> >> > I tried adding it like so:
>>
>> >> >> > Private Sub OK_Click()
>> >> >> > Dim db As Database
>> >> >> > Dim rs As Recordset
>> >> >> > Dim strCriteria As String
>>
>> >> >> > Set db = CurrentDb
>> >> >> > Set rs = db.OpenRecordset("qfltAdvisorLocations", dbOpenDynaset)
>>
>> >> >> > If Me.Dirty Then
>> >> >> > Me.Dirty = False
>> >> >> > End If
>> >> >> > ...etc.
>>
>> >> >> > I'm still getting the same problem.- Hide quoted text -
>>
>> >> >> > - Show quoted text -
>>
>> >> >> Never mind. I see what you're saying. I added it like this:
>>
>> >> >> Private Sub OK_Click()
>> >> >> Dim db As Database
>> >> >> Dim rs As Recordset
>> >> >> Dim strCriteria As String
>>
>> >> >> If Me.Dirty Then
>> >> >> Me.Dirty = False
>> >> >> End If
>>
>> >> >> Set db = CurrentDb
>> >> >> Set rs = db.OpenRecordset("qfltAdvisorLocations", dbOpenDynaset)
>> >> >> ...etc.
>>
>> >> >> and now it works fine. Thank you.- Hide quoted text -
>>
>> >> >> - Show quoted text -
>>
>> >> > Nevermind again. I'm still getting the same problem.
>>
>> >> On which line of your code do you get the error?
>>
>> >> Carl Rapson- Hide quoted text -
>>
>> >> - Show quoted text -
>>
>> > rs.movefirst
>>
>> What's the purpose of the recordset? If all you need is the new Advisor
>> ID,
>> you can get it directly from the form, you don't need to pull it from the
>> temporary table:
>>
>> DoCmd.OpenForm "frmAdvisorLocations"
>> Forms!frmAdvisorLocations![Advisor ID] = Me.[Advisor ID]
>>
>> I might also point out, in the section where you're checking for missing
>> fields, you'll need to put Exit Sub after each MsgBox, or else you'll
>> execute the remaining code as if the fields were all filled in.
>>
>> Carl Rapson- Hide quoted text -
>>
>> - Show quoted text -
>
> Honestly, after looking at the Dirty method a little bit, I'm starting
> to wonder if maybe I'm going about this all wrong. However, here's
> basically the answer to your question.
>
> There is no Advisor ID in tblNewAdvisor. I'm basically just using it
> to store the last name, first name, and email address of the new
> advisor until I'm ready to add it to tblAdvisors. Since this table is
> the record source for my form, I can't just say "Forms!
> frmAdvisorLocations![Advisor ID] = Me.[Advisor ID]" because there is
> no Advisor ID in my form.
>
> As far as your other concern, the only remaining code after the
> If...Then statement is as follows:
>
> Exit_OK_Click:
> Set db = Nothing
> Set rs = Nothing
> Exit Sub
>
> Since that's basically doing what you're suggesting, I'm not sure what
> the problem is. Am I misunderstanding something?
>

No, you're correct. I missed the Else in your If block.

I guess I don't understand where your new Advisor ID is coming from. What is
qfltAdvisorLocations, and how is it returning the new Advisor ID? Especially
since it's running before you run the qappCreateNewAdvisor query. Can you
give a little more detail about the sequence of events and what each query
is doing?

Carl Rapson