You can't go to the specified record

[Amour]

am really new to VBA,
currently I have one form were I create meetings, than another one were the
individual is inputted(frmAttendance = qryAttendan = tblAttend) I am opening
a form(frmTravel = qryTravel = tblTravel) from a button with in
frmAttendance. FrmTravel has fields with in that when I close that from I
want it to populate some fields on the frmAttendance form. Also there is a
query that I want one of the fields to populate one field with in
frmAttendance and also write that field to tblAttendance. Because I want
tblAttendance to come up with the information from tblTravel it looks like I
am going to have to have a query. So I tried adding on to the existing
query, but I get a message: " You can't go to the specified record"

Here is the SQL for that query:

SELECT DISTINCTROW tblAttendan.MEET_NUM, tblAttendan.SSN,
tblAttendan.LAST_NAME, tblAttendan.FY, tblAttendan.TRVL_START,
tblAttendan.TRVL_END, tblAttendan.TRVL_CODE, tblAttendan.ATT_START,
tblAttendan.ATT_END, tblAttendan.POSITION, tblAttendan.DAYS,
tblAttendan.PAY_CLASS, tblAttendan.PAY_RATE, tblAttendan.AMT_PAID,
tblAttendan.PAYRL_DATE, tblAttendan.EST_TRVL, tblAttendan.ACT_TRVL,
tblAttendan.TO_APG, tblAttendan.FROM_APG, tblAttendan.INITIAL,
tblAttendan.UDATE, tblAttendan.[Estimated Airfare], tblAttendan.[Estimated
Per Diem], tblAttendan.LAST_NAME AS indexlastname, tblPersonnel.LAST_NAME AS
perslast, tblPersonnel.STATUS, tblPersonnel.CONSL_TYPE,
tblPersonnel.FIRST_NAME, tblPersonnel.APP_DATE, tblPersonnel.ROTAT_DATE,
tblPersonnel.ORIG_DATE, tblPersonnel.CNSL_DATE, tblMeeting.TITLE_NUM,
tblMeeting.FY, tblMeeting.TYPE, tblMeeting.TYPE_CODE, tblMeeting.TITLE_NUM,
tblMeeting.TITLE, tblPersonnel.PAY_RATE AS perspay, tblAttendan.COMMENTS,
NZ(TblTravel.DateProcessed) AS DatePro, NZ(TblTravel.Airfare) AS Air
FROM tblMeeting INNER JOIN ((tblPersonnel INNER JOIN tblTravel ON
tblPersonnel.SSN = tblTravel.SSN) INNER JOIN tblAttendan ON tblPersonnel.SSN
= tblAttendan.SSN) ON tblMeeting.MEET_NUM = tblAttendan.MEET_NUM
WHERE (((tblAttendan.MEET_NUM)=[forms]![frmMeetings]![meet_num]))
ORDER BY tblAttendan.LAST_NAME, tblAttendan.ATT_START,
tblPersonnel.FIRST_NAME;


All I did was to add on the statement:
NZ(TblTravel.DateProcessed) AS DatePro, NZ(TblTravel.Airfare) AS Air

Then on the form in the control source I have:
DatePro and Air (this displays)

so what am I doing wrong...

Please help

Thank You!

 

[hmadyson]

I find that when things go kooky from just adding fields that are formulas,
it may be because it is having difficulty with the names. Please change the
field names from DatePro and Air to Expr1 and Expr2 to see if that clears up
anything. It could be causing a problem if there are fields called Air and
Datepro in other tables, or accessed elsewhere.

Let me know if I can provide more assistance.

am really new to VBA,
currently I have one form were I create meetings, than another one were the
individual is inputted(frmAttendance = qryAttendan = tblAttend) I am opening
a form(frmTravel = qryTravel = tblTravel) from a button with in
frmAttendance. FrmTravel has fields with in that when I close that from I
want it to populate some fields on the frmAttendance form. Also there is a
query that I want one of the fields to populate one field with in
frmAttendance and also write that field to tblAttendance. Because I want
tblAttendance to come up with the information from tblTravel it looks like I
am going to have to have a query. So I tried adding on to the existing
query, but I get a message: " You can't go to the specified record"

Here is the SQL for that query:

SELECT DISTINCTROW tblAttendan.MEET_NUM, tblAttendan.SSN,
tblAttendan.LAST_NAME, tblAttendan.FY, tblAttendan.TRVL_START,
tblAttendan.TRVL_END, tblAttendan.TRVL_CODE, tblAttendan.ATT_START,
tblAttendan.ATT_END, tblAttendan.POSITION, tblAttendan.DAYS,
tblAttendan.PAY_CLASS, tblAttendan.PAY_RATE, tblAttendan.AMT_PAID,
tblAttendan.PAYRL_DATE, tblAttendan.EST_TRVL, tblAttendan.ACT_TRVL,
tblAttendan.TO_APG, tblAttendan.FROM_APG, tblAttendan.INITIAL,
tblAttendan.UDATE, tblAttendan.[Estimated Airfare], tblAttendan.[Estimated
Per Diem], tblAttendan.LAST_NAME AS indexlastname, tblPersonnel.LAST_NAME AS
perslast, tblPersonnel.STATUS, tblPersonnel.CONSL_TYPE,
tblPersonnel.FIRST_NAME, tblPersonnel.APP_DATE, tblPersonnel.ROTAT_DATE,
tblPersonnel.ORIG_DATE, tblPersonnel.CNSL_DATE, tblMeeting.TITLE_NUM,
tblMeeting.FY, tblMeeting.TYPE, tblMeeting.TYPE_CODE, tblMeeting.TITLE_NUM,
tblMeeting.TITLE, tblPersonnel.PAY_RATE AS perspay, tblAttendan.COMMENTS,
NZ(TblTravel.DateProcessed) AS DatePro, NZ(TblTravel.Airfare) AS Air
FROM tblMeeting INNER JOIN ((tblPersonnel INNER JOIN tblTravel ON
tblPersonnel.SSN = tblTravel.SSN) INNER JOIN tblAttendan ON tblPersonnel.SSN
= tblAttendan.SSN) ON tblMeeting.MEET_NUM = tblAttendan.MEET_NUM
WHERE (((tblAttendan.MEET_NUM)=[forms]![frmMeetings]![meet_num]))
ORDER BY tblAttendan.LAST_NAME, tblAttendan.ATT_START,
tblPersonnel.FIRST_NAME;


All I did was to add on the statement:
NZ(TblTravel.DateProcessed) AS DatePro, NZ(TblTravel.Airfare) AS Air

Then on the form in the control source I have:
DatePro and Air (this displays)

so what am I doing wrong...

Please help

Thank You!

 

[Amour]

Hi, and thank you for responding.

I renamed like you indicated, but I still have the same error: "You can't
go to the specified record"


Here again is the code with the changes:

SELECT DISTINCTROW tblAttendan.MEET_NUM, tblAttendan.SSN,
tblAttendan.LAST_NAME, tblAttendan.FY, tblAttendan.TRVL_START,
tblAttendan.TRVL_END, tblAttendan.TRVL_CODE, tblAttendan.ATT_START,
tblAttendan.ATT_END, tblAttendan.POSITION, tblAttendan.DAYS,
tblAttendan.PAY_CLASS, tblAttendan.PAY_RATE, tblAttendan.AMT_PAID,
tblAttendan.PAYRL_DATE, tblAttendan.EST_TRVL, tblAttendan.ACT_TRVL,
tblAttendan.TO_APG, tblAttendan.FROM_APG, tblAttendan.INITIAL,
tblAttendan.UDATE, tblAttendan.[Estimated Airfare], tblAttendan.[Estimated
Per Diem], tblAttendan.LAST_NAME AS indexlastname, tblPersonnel.LAST_NAME AS
perslast, tblPersonnel.STATUS, tblPersonnel.CONSL_TYPE,
tblPersonnel.FIRST_NAME, tblPersonnel.APP_DATE, tblPersonnel.ROTAT_DATE,
tblPersonnel.ORIG_DATE, tblPersonnel.CNSL_DATE, tblMeeting.TITLE_NUM,
tblMeeting.FY, tblMeeting.TYPE, tblMeeting.TYPE_CODE, tblMeeting.TITLE_NUM,
tblMeeting.TITLE, tblPersonnel.PAY_RATE AS perspay, tblAttendan.COMMENTS,
NZ(TblTravel.DateProcessed) AS Expr1, NZ(TblTravel.Airfare) AS Expr2
FROM tblMeeting INNER JOIN ((tblPersonnel INNER JOIN tblTravel ON
tblPersonnel.SSN = tblTravel.SSN) INNER JOIN tblAttendan ON tblPersonnel.SSN
= tblAttendan.SSN) ON tblMeeting.MEET_NUM = tblAttendan.MEET_NUM
WHERE (((tblAttendan.MEET_NUM)=[forms]![frmMeetings]![meet_num]))
ORDER BY tblAttendan.LAST_NAME, tblAttendan.ATT_START,
tblPersonnel.FIRST_NAME;

And this is the part were I renamed:

NZ(TblTravel.DateProcessed) AS Expr1, NZ(TblTravel.Airfare) AS Expr2

I find that when things go kooky from just adding fields that are formulas,
it may be because it is having difficulty with the names. Please change the
field names from DatePro and Air to Expr1 and Expr2 to see if that clears up
anything. It could be causing a problem if there are fields called Air and
Datepro in other tables, or accessed elsewhere.

Let me know if I can provide more assistance.

am really new to VBA,
currently I have one form were I create meetings, than another one were the
individual is inputted(frmAttendance = qryAttendan = tblAttend) I am opening
a form(frmTravel = qryTravel = tblTravel) from a button with in
frmAttendance. FrmTravel has fields with in that when I close that from I
want it to populate some fields on the frmAttendance form. Also there is a
query that I want one of the fields to populate one field with in
frmAttendance and also write that field to tblAttendance. Because I want
tblAttendance to come up with the information from tblTravel it looks like I
am going to have to have a query. So I tried adding on to the existing
query, but I get a message: " You can't go to the specified record"

Here is the SQL for that query:

SELECT DISTINCTROW tblAttendan.MEET_NUM, tblAttendan.SSN,
tblAttendan.LAST_NAME, tblAttendan.FY, tblAttendan.TRVL_START,
tblAttendan.TRVL_END, tblAttendan.TRVL_CODE, tblAttendan.ATT_START,
tblAttendan.ATT_END, tblAttendan.POSITION, tblAttendan.DAYS,
tblAttendan.PAY_CLASS, tblAttendan.PAY_RATE, tblAttendan.AMT_PAID,
tblAttendan.PAYRL_DATE, tblAttendan.EST_TRVL, tblAttendan.ACT_TRVL,
tblAttendan.TO_APG, tblAttendan.FROM_APG, tblAttendan.INITIAL,
tblAttendan.UDATE, tblAttendan.[Estimated Airfare], tblAttendan.[Estimated
Per Diem], tblAttendan.LAST_NAME AS indexlastname, tblPersonnel.LAST_NAME AS
perslast, tblPersonnel.STATUS, tblPersonnel.CONSL_TYPE,
tblPersonnel.FIRST_NAME, tblPersonnel.APP_DATE, tblPersonnel.ROTAT_DATE,
tblPersonnel.ORIG_DATE, tblPersonnel.CNSL_DATE, tblMeeting.TITLE_NUM,
tblMeeting.FY, tblMeeting.TYPE, tblMeeting.TYPE_CODE, tblMeeting.TITLE_NUM,
tblMeeting.TITLE, tblPersonnel.PAY_RATE AS perspay, tblAttendan.COMMENTS,
NZ(TblTravel.DateProcessed) AS DatePro, NZ(TblTravel.Airfare) AS Air
FROM tblMeeting INNER JOIN ((tblPersonnel INNER JOIN tblTravel ON
tblPersonnel.SSN = tblTravel.SSN) INNER JOIN tblAttendan ON tblPersonnel.SSN
= tblAttendan.SSN) ON tblMeeting.MEET_NUM = tblAttendan.MEET_NUM
WHERE (((tblAttendan.MEET_NUM)=[forms]![frmMeetings]![meet_num]))
ORDER BY tblAttendan.LAST_NAME, tblAttendan.ATT_START,
tblPersonnel.FIRST_NAME;


All I did was to add on the statement:
NZ(TblTravel.DateProcessed) AS DatePro, NZ(TblTravel.Airfare) AS Air

Then on the form in the control source I have:
DatePro and Air (this displays)

so what am I doing wrong...

Please help

Thank You!

 

[Amour]

Thank You for any help.

I wanted to mention that I don't get the error message "You can't go to the
specified record" until I press the add new record button within the
frmAttendance:

This is the code behind that:

Private Sub btnAddNew_Click()
On Error GoTo Err_btnAddNew_Click
Me.DefaultEditing = 1
Me!BoxNoRec.Visible = False
Me!cbxSSN.Visible = True
Me!Last_Name.Visible = False
DoCmd.GoToRecord , , A_NEWREC
Me!cbxSSN.SetFocus
Exit_btnAddNew_Click:
Exit Sub

Err_btnAddNew_Click:
MsgBox Error$
Resume Exit_btnAddNew_Click

End Sub


Thank You...

I find that when things go kooky from just adding fields that are formulas,
it may be because it is having difficulty with the names. Please change the
field names from DatePro and Air to Expr1 and Expr2 to see if that clears up
anything. It could be causing a problem if there are fields called Air and
Datepro in other tables, or accessed elsewhere.

Let me know if I can provide more assistance.

am really new to VBA,
currently I have one form were I create meetings, than another one were the
individual is inputted(frmAttendance = qryAttendan = tblAttend) I am opening
a form(frmTravel = qryTravel = tblTravel) from a button with in
frmAttendance. FrmTravel has fields with in that when I close that from I
want it to populate some fields on the frmAttendance form. Also there is a
query that I want one of the fields to populate one field with in
frmAttendance and also write that field to tblAttendance. Because I want
tblAttendance to come up with the information from tblTravel it looks like I
am going to have to have a query. So I tried adding on to the existing
query, but I get a message: " You can't go to the specified record"

Here is the SQL for that query:

SELECT DISTINCTROW tblAttendan.MEET_NUM, tblAttendan.SSN,
tblAttendan.LAST_NAME, tblAttendan.FY, tblAttendan.TRVL_START,
tblAttendan.TRVL_END, tblAttendan.TRVL_CODE, tblAttendan.ATT_START,
tblAttendan.ATT_END, tblAttendan.POSITION, tblAttendan.DAYS,
tblAttendan.PAY_CLASS, tblAttendan.PAY_RATE, tblAttendan.AMT_PAID,
tblAttendan.PAYRL_DATE, tblAttendan.EST_TRVL, tblAttendan.ACT_TRVL,
tblAttendan.TO_APG, tblAttendan.FROM_APG, tblAttendan.INITIAL,
tblAttendan.UDATE, tblAttendan.[Estimated Airfare], tblAttendan.[Estimated
Per Diem], tblAttendan.LAST_NAME AS indexlastname, tblPersonnel.LAST_NAME AS
perslast, tblPersonnel.STATUS, tblPersonnel.CONSL_TYPE,
tblPersonnel.FIRST_NAME, tblPersonnel.APP_DATE, tblPersonnel.ROTAT_DATE,
tblPersonnel.ORIG_DATE, tblPersonnel.CNSL_DATE, tblMeeting.TITLE_NUM,
tblMeeting.FY, tblMeeting.TYPE, tblMeeting.TYPE_CODE, tblMeeting.TITLE_NUM,
tblMeeting.TITLE, tblPersonnel.PAY_RATE AS perspay, tblAttendan.COMMENTS,
NZ(TblTravel.DateProcessed) AS DatePro, NZ(TblTravel.Airfare) AS Air
FROM tblMeeting INNER JOIN ((tblPersonnel INNER JOIN tblTravel ON
tblPersonnel.SSN = tblTravel.SSN) INNER JOIN tblAttendan ON tblPersonnel.SSN
= tblAttendan.SSN) ON tblMeeting.MEET_NUM = tblAttendan.MEET_NUM
WHERE (((tblAttendan.MEET_NUM)=[forms]![frmMeetings]![meet_num]))
ORDER BY tblAttendan.LAST_NAME, tblAttendan.ATT_START,
tblPersonnel.FIRST_NAME;


All I did was to add on the statement:
NZ(TblTravel.DateProcessed) AS DatePro, NZ(TblTravel.Airfare) AS Air

Then on the form in the control source I have:
DatePro and Air (this displays)

so what am I doing wrong...

Please help

Thank You!

 

[hmadyson]

If you would like, you can send your project to me at
k--a--r--e--n--y--y--y--1--at--c--o--m--c--a--s--t--dot--n--e--t
(remove the double dash and make it like an email address)

Thank You for any help.

I wanted to mention that I don't get the error message "You can't go to the
specified record" until I press the add new record button within the
frmAttendance:

This is the code behind that:

Private Sub btnAddNew_Click()
On Error GoTo Err_btnAddNew_Click
Me.DefaultEditing = 1
Me!BoxNoRec.Visible = False
Me!cbxSSN.Visible = True
Me!Last_Name.Visible = False
DoCmd.GoToRecord , , A_NEWREC
Me!cbxSSN.SetFocus
Exit_btnAddNew_Click:
Exit Sub

Err_btnAddNew_Click:
MsgBox Error$
Resume Exit_btnAddNew_Click

End Sub


Thank You...

I find that when things go kooky from just adding fields that are formulas,
it may be because it is having difficulty with the names. Please change the
field names from DatePro and Air to Expr1 and Expr2 to see if that clears up
anything. It could be causing a problem if there are fields called Air and
Datepro in other tables, or accessed elsewhere.

Let me know if I can provide more assistance.

am really new to VBA,
currently I have one form were I create meetings, than another one were the
individual is inputted(frmAttendance = qryAttendan = tblAttend) I am opening
a form(frmTravel = qryTravel = tblTravel) from a button with in
frmAttendance. FrmTravel has fields with in that when I close that from I
want it to populate some fields on the frmAttendance form. Also there is a
query that I want one of the fields to populate one field with in
frmAttendance and also write that field to tblAttendance. Because I want
tblAttendance to come up with the information from tblTravel it looks like I
am going to have to have a query. So I tried adding on to the existing
query, but I get a message: " You can't go to the specified record"

Here is the SQL for that query:

SELECT DISTINCTROW tblAttendan.MEET_NUM, tblAttendan.SSN,
tblAttendan.LAST_NAME, tblAttendan.FY, tblAttendan.TRVL_START,
tblAttendan.TRVL_END, tblAttendan.TRVL_CODE, tblAttendan.ATT_START,
tblAttendan.ATT_END, tblAttendan.POSITION, tblAttendan.DAYS,
tblAttendan.PAY_CLASS, tblAttendan.PAY_RATE, tblAttendan.AMT_PAID,
tblAttendan.PAYRL_DATE, tblAttendan.EST_TRVL, tblAttendan.ACT_TRVL,
tblAttendan.TO_APG, tblAttendan.FROM_APG, tblAttendan.INITIAL,
tblAttendan.UDATE, tblAttendan.[Estimated Airfare], tblAttendan.[Estimated
Per Diem], tblAttendan.LAST_NAME AS indexlastname, tblPersonnel.LAST_NAME AS
perslast, tblPersonnel.STATUS, tblPersonnel.CONSL_TYPE,
tblPersonnel.FIRST_NAME, tblPersonnel.APP_DATE, tblPersonnel.ROTAT_DATE,
tblPersonnel.ORIG_DATE, tblPersonnel.CNSL_DATE, tblMeeting.TITLE_NUM,
tblMeeting.FY, tblMeeting.TYPE, tblMeeting.TYPE_CODE, tblMeeting.TITLE_NUM,
tblMeeting.TITLE, tblPersonnel.PAY_RATE AS perspay, tblAttendan.COMMENTS,
NZ(TblTravel.DateProcessed) AS DatePro, NZ(TblTravel.Airfare) AS Air
FROM tblMeeting INNER JOIN ((tblPersonnel INNER JOIN tblTravel ON
tblPersonnel.SSN = tblTravel.SSN) INNER JOIN tblAttendan ON tblPersonnel.SSN
= tblAttendan.SSN) ON tblMeeting.MEET_NUM = tblAttendan.MEET_NUM
WHERE (((tblAttendan.MEET_NUM)=[forms]![frmMeetings]![meet_num]))
ORDER BY tblAttendan.LAST_NAME, tblAttendan.ATT_START,
tblPersonnel.FIRST_NAME;


All I did was to add on the statement:
NZ(TblTravel.DateProcessed) AS DatePro, NZ(TblTravel.Airfare) AS Air

Then on the form in the control source I have:
DatePro and Air (this displays)

so what am I doing wrong...

Please help

Thank You!