Popluating a form

A

Amour

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 form 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...
 
C

Carl Rapson

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 form 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...
Click to expand...

"Updatable" includes adding new records. If the underlying query isn't
updatable, you won't be able to add new records.

Carl Rapson
 
A

Amour

First of all thank you for your response.
This query is updatable. like I said in the post when I *ONLY* add the new
section:
NZ(TblTravel.DateProcessed) AS Expr1, NZ(TblTravel.Airfare) AS Expr2 do I
get this message: "You can't go to the specified record" otherwise it works
great and add/updates. Were am I updating when I add this table? I only
want to display the information on the form from another table. All I am
doing is adding a table to a current query and display the fields on the form.

Please help and thank you!



Carl Rapson said:
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 form 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...
Click to expand...

"Updatable" includes adding new records. If the underlying query isn't
updatable, you won't be able to add new records.

Carl Rapson
Click to expand...
 
J

John W. Vinson

First of all thank you for your response.
This query is updatable. like I said in the post when I *ONLY* add the new
section:
NZ(TblTravel.DateProcessed) AS Expr1, NZ(TblTravel.Airfare) AS Expr2 do I
get this message: "You can't go to the specified record" otherwise it works
great and add/updates. Were am I updating when I add this table? I only
want to display the information on the form from another table. All I am
doing is adding a table to a current query and display the fields on the form.
Click to expand...

If you're using a Query as the Recordsource for a form, then Access is
assuming that you want to update the records in that query; so the query must
be updateable.

You can't just throw another table into a Form's Recordsource query and expect
everything to work the same. It won't!!!

If you just want to *display* two fields from a single record in tblTravel on
the form, consider using two textboxes with DLookUp functions to do so. If
tblTravel is related one to many to the main form's table, you might instead
want to put a Subform based on tblTravel onto the form, so that you can view
(and if desired, edit) the data in that table.

John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

adding a record 0
Popluating a form from another table 1
recordsource updatable 3
You can't go to the specified record 4
You can't go to the specified record 0
Populating fields 3
Moving fields 0
Dlookup Oject error 0

Top