Recordsets and Forms

[DoveArrow]

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

 

[Carl Rapson]

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.) 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?
[/QUOTE]

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

 

[DoveArrow]

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.) Then
MsgBox "You have not completed filling out this form."
Else
Me.Refresh
DoCmd.SetWarnings False[/QUOTE]
[QUOTE]
'Create New Advisor
DoCmd.OpenQuery "qappCreateNewAdvisor"[/QUOTE]
[QUOTE]
'Select Advisor ID for frmAdvisorLocations
On Error GoTo Err_OK_Click
rs.MoveFirst
strCriteria = rs![Advisor ID][/QUOTE]
[QUOTE]
'Open frmAdvisorLocations
DoCmd.OpenForm "frmAdvisorLocations"
Forms!frmAdvisorLocations![Advisor ID] = strCriteria[/QUOTE]
[QUOTE]
'Close frmCreateAdvisor
DoCmd.Close acForm, "frmCreateAdvisor", acSavePrompt
DoCmd.SetWarnings True
End If[/QUOTE]
[QUOTE]
Exit_OK_Click:
Set db = Nothing
Set rs = Nothing
Exit Sub[/QUOTE]
[QUOTE]
Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click[/QUOTE]
[QUOTE]
End Sub[/QUOTE]
[QUOTE]
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?[/QUOTE]

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 -[/QUOTE]

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.

 

[DoveArrow]

news:[email protected]...

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.) 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?[/QUOTE][/QUOTE]
[QUOTE]
I suspect that you first need to save the current record before running the
query. Try adding this to your Click event:[/QUOTE]
[QUOTE]
If Me.Dirty Then Me.Dirty = False
Set db = CurrentDb
'...[/QUOTE]
[QUOTE]
Carl Rapson- Hide quoted text -[/QUOTE]
[QUOTE]
- Show quoted text -[/QUOTE]

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 -[/QUOTE]

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.

 

[DoveArrow]

On Oct 18, 8:05 am, "Carl Rapson" <[email protected]>
wrote:

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.) 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 -[/QUOTE][/QUOTE]
[QUOTE]
I tried adding it like so:[/QUOTE]
[QUOTE]
Private Sub OK_Click()
Dim db As Database
Dim rs As Recordset
Dim strCriteria As String[/QUOTE]
[QUOTE]
Set db = CurrentDb
Set rs = db.OpenRecordset("qfltAdvisorLocations", dbOpenDynaset)[/QUOTE]
[QUOTE]
If Me.Dirty Then
Me.Dirty = False
End If
...etc.[/QUOTE]
[QUOTE]
I'm still getting the same problem.- Hide quoted text -[/QUOTE]
[QUOTE]
- Show quoted text -[/QUOTE]

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 -[/QUOTE]

Nevermind again. I'm still getting the same problem.

 

[Carl Rapson]

On Oct 18, 8:05 am, "Carl Rapson" <[email protected]>
wrote:

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.) Then
MsgBox "You have not completed filling out this form."
Else
Me.Refresh
DoCmd.SetWarnings False[/QUOTE]
[QUOTE]
'Create New Advisor
DoCmd.OpenQuery "qappCreateNewAdvisor"[/QUOTE]
[QUOTE]
'Select Advisor ID for frmAdvisorLocations
On Error GoTo Err_OK_Click
rs.MoveFirst
strCriteria = rs![Advisor ID][/QUOTE]
[QUOTE]
'Open frmAdvisorLocations
DoCmd.OpenForm "frmAdvisorLocations"
Forms!frmAdvisorLocations![Advisor ID] = strCriteria[/QUOTE]
[QUOTE]
'Close frmCreateAdvisor
DoCmd.Close acForm, "frmCreateAdvisor", acSavePrompt
DoCmd.SetWarnings True
End If[/QUOTE]
[QUOTE]
Exit_OK_Click:
Set db = Nothing
Set rs = Nothing
Exit Sub[/QUOTE]
[QUOTE]
Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click[/QUOTE]
[QUOTE]
End Sub[/QUOTE]
[QUOTE]
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?[/QUOTE]
[QUOTE]
I suspect that you first need to save the current record before
running the
query. Try adding this to your Click event:[/QUOTE]
[QUOTE]
If Me.Dirty Then Me.Dirty = False
Set db = CurrentDb
'...[/QUOTE]
[QUOTE]
Carl Rapson- Hide quoted text -[/QUOTE]
[QUOTE]
- Show quoted text -[/QUOTE]
[QUOTE]
I tried adding it like so:[/QUOTE]
[QUOTE]
Private Sub OK_Click()
Dim db As Database
Dim rs As Recordset
Dim strCriteria As String[/QUOTE]
[QUOTE]
Set db = CurrentDb
Set rs = db.OpenRecordset("qfltAdvisorLocations", dbOpenDynaset)[/QUOTE]
[QUOTE]
If Me.Dirty Then
Me.Dirty = False
End If
...etc.[/QUOTE]
[QUOTE]
I'm still getting the same problem.- Hide quoted text -[/QUOTE]
[QUOTE]
- Show quoted text -[/QUOTE]

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 -[/QUOTE]

Nevermind again. I'm still getting the same problem.
[/QUOTE]

On which line of your code do you get the error?

Carl Rapson

 

[DoveArrow]

On Oct 18, 8:05 am, "Carl Rapson" <[email protected]>
wrote:

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.) 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 -[/QUOTE][/QUOTE]
[QUOTE]
Nevermind again. I'm still getting the same problem.[/QUOTE]

On which line of your code do you get the error?

Carl Rapson- Hide quoted text -

- Show quoted text -[/QUOTE]

rs.movefirst

 

[connie]

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.) 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?
[/QUOTE]

 

[Carl Rapson]

On Oct 18, 2:02 pm, DoveArrow <[email protected]> wrote:

On Oct 18, 8:05 am, "Carl Rapson" <[email protected]>
wrote:

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.) Then
MsgBox "You have not completed filling out this form."
Else
Me.Refresh
DoCmd.SetWarnings False[/QUOTE]
[QUOTE]
'Create New Advisor
DoCmd.OpenQuery "qappCreateNewAdvisor"[/QUOTE]
[QUOTE]
'Select Advisor ID for frmAdvisorLocations
On Error GoTo Err_OK_Click
rs.MoveFirst
strCriteria = rs![Advisor ID][/QUOTE]
[QUOTE]
'Open frmAdvisorLocations
DoCmd.OpenForm "frmAdvisorLocations"
Forms!frmAdvisorLocations![Advisor ID] = strCriteria[/QUOTE]
[QUOTE]
'Close frmCreateAdvisor
DoCmd.Close acForm, "frmCreateAdvisor", acSavePrompt
DoCmd.SetWarnings True
End If[/QUOTE]
[QUOTE]
Exit_OK_Click:
Set db = Nothing
Set rs = Nothing
Exit Sub[/QUOTE]
[QUOTE]
Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click[/QUOTE]
[QUOTE]
End Sub[/QUOTE]
[QUOTE]
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?[/QUOTE]
[QUOTE]
I suspect that you first need to save the current record before
running the
query. Try adding this to your Click event:[/QUOTE]
[QUOTE]
If Me.Dirty Then Me.Dirty = False
Set db = CurrentDb
'...[/QUOTE]
[QUOTE]
Carl Rapson- Hide quoted text -[/QUOTE]
[QUOTE]
- Show quoted text -[/QUOTE]
[QUOTE]
I tried adding it like so:[/QUOTE]
[QUOTE]
Private Sub OK_Click()
Dim db As Database
Dim rs As Recordset
Dim strCriteria As String[/QUOTE]
[QUOTE]
Set db = CurrentDb
Set rs = db.OpenRecordset("qfltAdvisorLocations", dbOpenDynaset)[/QUOTE]
[QUOTE]
If Me.Dirty Then
Me.Dirty = False
End If
...etc.[/QUOTE]
[QUOTE]
I'm still getting the same problem.- Hide quoted text -[/QUOTE]
[QUOTE]
- Show quoted text -[/QUOTE]
[QUOTE]
Never mind. I see what you're saying. I added it like this:[/QUOTE]
[QUOTE]
Private Sub OK_Click()
Dim db As Database
Dim rs As Recordset
Dim strCriteria As String[/QUOTE]
[QUOTE]
If Me.Dirty Then
Me.Dirty = False
End If[/QUOTE]
[QUOTE]
Set db = CurrentDb
Set rs = db.OpenRecordset("qfltAdvisorLocations", dbOpenDynaset)
...etc.[/QUOTE]
[QUOTE]
and now it works fine. Thank you.- Hide quoted text -[/QUOTE]
[QUOTE]
- Show quoted text -[/QUOTE]
[QUOTE]
Nevermind again. I'm still getting the same problem.[/QUOTE]

On which line of your code do you get the error?

Carl Rapson- Hide quoted text -

- Show quoted text -[/QUOTE]

rs.movefirst
[/QUOTE]

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

 

[DoveArrow]

On Oct 18, 8:05 am, "Carl Rapson" <[email protected]>
wrote:

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.) 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 -[/QUOTE][/QUOTE]
[QUOTE]
rs.movefirst[/QUOTE]

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 -[/QUOTE]

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?

 

[Carl Rapson]

On Oct 19, 7:09 am, "Carl Rapson" <[email protected]>
wrote:


On Oct 18, 2:02 pm, DoveArrow <[email protected]> wrote:

On Oct 18, 8:05 am, "Carl Rapson" <[email protected]>
wrote:

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.) Then
MsgBox "You have not completed filling out this form."
Else
Me.Refresh
DoCmd.SetWarnings False[/QUOTE]
[QUOTE]
'Create New Advisor
DoCmd.OpenQuery "qappCreateNewAdvisor"[/QUOTE]
[QUOTE]
'Select Advisor ID for frmAdvisorLocations
On Error GoTo Err_OK_Click
rs.MoveFirst
strCriteria = rs![Advisor ID][/QUOTE]
[QUOTE]
'Open frmAdvisorLocations
DoCmd.OpenForm "frmAdvisorLocations"
Forms!frmAdvisorLocations![Advisor ID] = strCriteria[/QUOTE]
[QUOTE]
'Close frmCreateAdvisor
DoCmd.Close acForm, "frmCreateAdvisor", acSavePrompt
DoCmd.SetWarnings True
End If[/QUOTE]
[QUOTE]
Exit_OK_Click:
Set db = Nothing
Set rs = Nothing
Exit Sub[/QUOTE]
[QUOTE]
Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click[/QUOTE]
[QUOTE]
End Sub[/QUOTE]
[QUOTE]
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?[/QUOTE]
[QUOTE]
I suspect that you first need to save the current record before
running the
query. Try adding this to your Click event:[/QUOTE]
[QUOTE]
If Me.Dirty Then Me.Dirty = False
Set db = CurrentDb
'...[/QUOTE]
[QUOTE]
Carl Rapson- Hide quoted text -[/QUOTE]
[QUOTE]
- Show quoted text -[/QUOTE]
[QUOTE]
I tried adding it like so:[/QUOTE]
[QUOTE]
Private Sub OK_Click()
Dim db As Database
Dim rs As Recordset
Dim strCriteria As String[/QUOTE]
[QUOTE]
Set db = CurrentDb
Set rs = db.OpenRecordset("qfltAdvisorLocations", dbOpenDynaset)[/QUOTE]
[QUOTE]
If Me.Dirty Then
Me.Dirty = False
End If
...etc.[/QUOTE]
[QUOTE]
I'm still getting the same problem.- Hide quoted text -[/QUOTE]
[QUOTE]
- Show quoted text -[/QUOTE]
[QUOTE]
Never mind. I see what you're saying. I added it like this:[/QUOTE]
[QUOTE]
Private Sub OK_Click()
Dim db As Database
Dim rs As Recordset
Dim strCriteria As String[/QUOTE]
[QUOTE]
If Me.Dirty Then
Me.Dirty = False
End If[/QUOTE]
[QUOTE]
Set db = CurrentDb
Set rs = db.OpenRecordset("qfltAdvisorLocations", dbOpenDynaset)
...etc.[/QUOTE]
[QUOTE]
and now it works fine. Thank you.- Hide quoted text -[/QUOTE]
[QUOTE]
- Show quoted text -[/QUOTE]
[QUOTE]
Nevermind again. I'm still getting the same problem.[/QUOTE]
[QUOTE]
On which line of your code do you get the error?[/QUOTE]
[QUOTE]
Carl Rapson- Hide quoted text -[/QUOTE]
[QUOTE]
- Show quoted text -[/QUOTE]
[QUOTE]
rs.movefirst[/QUOTE]

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 -[/QUOTE]

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?
[/QUOTE]

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

 

[DoveArrow]

On Oct 19, 7:09 am, "Carl Rapson" <[email protected]>
wrote:

On Oct 18, 8:05 am, "Carl Rapson" <[email protected]>
wrote:

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.) 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 -[/QUOTE][/QUOTE]
[QUOTE]
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.[/QUOTE]
[QUOTE]
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.[/QUOTE]
[QUOTE]
As far as your other concern, the only remaining code after the
If...Then statement is as follows:[/QUOTE]
[QUOTE]
Exit_OK_Click:
Set db = Nothing
Set rs = Nothing
Exit Sub[/QUOTE]
[QUOTE]
Since that's basically doing what you're suggesting, I'm not sure what
the problem is. Am I misunderstanding something?[/QUOTE]

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- Hide quoted text -

- Show quoted text -[/QUOTE]

Well basically, it's pulling the last name, first name, and Email
address entered into tblNewAdvisor and appending it to tblAdvisors,
but only when the Email address doesn't already exist (this was my
attempt to prevent people from creating duplicate records). Here's the
SQL, if you're curious.

INSERT INTO tblAdvisors ( [Last Name], [First Name], [Contact E-
Mail] )
SELECT tblNewAdvisor.[Last Name], tblNewAdvisor.[First Name],
tblNewAdvisor.[Contact E-Mail]
FROM tblNewAdvisor LEFT JOIN tblAdvisors ON tblNewAdvisor.[Contact E-
Mail] = tblAdvisors.[Contact E-Mail]
WHERE (((tblAdvisors.[Contact E-Mail]) Is Null));

Now here's the thing. You mentioned something about the Dirty
Property. I'm still a bit new to programming in Visual Basic, so this
was a new term for me. In any event, I started googling what it meant,
and after researching it, I decided to redesign my form (Note: Never
type the word "Dirty" by itself into a Google search. It doesn't pull
anything having to do with Visual Basic). Here's what I came up with.

Private Sub Cancel_Click()
On Error GoTo Cancel_Click_Error
Me.Undo
DoCmd.Close acForm, "Form1"

Exit_Cancel_Click:
Exit Sub

Cancel_Click_Error:
MsgBox Err.Description
Resume Exit_Cancel_Click

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo Form_BeforeUpdate_Err

If Me.Dirty Then
Select Case MsgBox("Do you want to save changes before quitting?",
vbYesNoCancel)
Case vbYes
Me.Dirty = False
Case vbNo
Me.Undo
Case vbCancel
Exit Sub
Case Else
Stop
End Select
End If

Form_BeforeUpdate_Exit:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Err.Description
Resume Form_BeforeUpdate_Exit

End Sub

This does pretty much what I want. The only thing I want it to do now
is if a particular Email address already exists, I want the computer
to prompt the user with a message like "An advisor with this Email
address already exists. Do you wish to continue?" Note: I realize that
my first form didn't even do this, it just didn't add the record.
However, I'm beginning to realize that even though the Email address
is a pretty unique identifier, if Bilbo Baggins leaves, and five years
later, a Frodo Baggins comes along, it's possible that they might
recycle Bilbo's old Email address and give it to Frodo. So I want the
computer to prompt the user, and ask them if they want to create a
duplicate record, rather than flat out denying them the ability to do
so. I have another thread going for this, so if you don't know the
answer to this question, let me just say that I'm eternally grateful
for your help in trying to resolve this matter. Thanks.

 

[DoveArrow]

news:[email protected]...

On Oct 22, 6:42 am, "Carl Rapson" <[email protected]>
wrote:

On Oct 19, 7:09 am, "Carl Rapson" <[email protected]>
wrote:

On Oct 18, 8:05 am, "Carl Rapson" <[email protected]>
wrote:

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 campusesthat
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 beused
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 thenew
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.) 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, andI'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?[/QUOTE][/QUOTE]
[QUOTE]
No, you're correct. I missed the Else in your If block.[/QUOTE]
[QUOTE]
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?[/QUOTE]
[QUOTE]
Carl Rapson- Hide quoted text -[/QUOTE]
[QUOTE]
- Show quoted text -[/QUOTE]

Well basically, it's pulling the last name, first name, and Email ...

read more »- Hide quoted text -

- Show quoted text -[/QUOTE]

Update: Someone in my other thread came up with a really elegant
solution, so now my form does everything I want it to do. In any case,
I want to say thank you for helping me out with this.

 

[Carl Rapson]

Glad it worked out for you.

Carl Rapson

On Oct 23, 8:28 am, "Carl Rapson" <[email protected]>
wrote:

<snipped>

Update: Someone in my other thread came up with a really elegant
solution, so now my form does everything I want it to do. In any case,
I want to say thank you for helping me out with this.