recordsource updatable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I 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 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;

This is the new section were I add to the query, yet when I take it out it
works fine:

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

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


Now I have read were you need to have the query as a recordsource updatable,
I don't understand. I am not trying to update anything. All I want to do is
display information from another table.

Please help

Thank You...
 
Are you saying you're able to go to a new record when you don't have those
fields in your query? I wouldn't have thought you'd be able to, since the
presence of DISTINCTROW means that the query isn't updatable.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Amour said:
I 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 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;

This is the new section were I add to the query, yet when I take it out it
works fine:

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

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


Now I have read were you need to have the query as a recordsource
updatable,
I don't understand. I am not trying to update anything. All I want to do
is
display information from another table.

Please help

Thank You...
 
Douglas said:
Are you saying you're able to go to a new record when you don't have
those fields in your query? I wouldn't have thought you'd be able to,
since the presence of DISTINCTROW means that the query isn't
updatable.

I believe it is only DISTINCT that renders a query non-editable, not
DISTINCTROW. In fact there are occassions where a non-editable query can be
made editable by using DISTINCTROW.
 
Thank you for your response....
Yes, I have been doing some testing and when I add the table to the query
(without joining) I get the fields but because of not joining it does not
display the correct record. If I join then it will display the correct
record but I get the message "You can't go to the specified record". If I
don't add the table than it works fine, but I need it to display fields on
the form (I think).

Please help


Douglas J. Steele said:
Are you saying you're able to go to a new record when you don't have those
fields in your query? I wouldn't have thought you'd be able to, since the
presence of DISTINCTROW means that the query isn't updatable.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Amour said:
I 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 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;

This is the new section were I add to the query, yet when I take it out it
works fine:

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

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


Now I have read were you need to have the query as a recordsource
updatable,
I don't understand. I am not trying to update anything. All I want to do
is
display information from another table.

Please help

Thank You...
 
Back
Top