Popluating a form from another table

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

This query is updatable. like I said in this 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. I am not 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.

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

This query is updatable. like I said in this 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. I am not 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.

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 am not trying to update anything. All I want to do is
display information from another table.

Please help

Thank You....
Click to expand...

You already have an Expr1 and Expr2 defined in your existing query:

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

Be sure that the new fields use different aliases.

What the "updatable" advice is referring to is that just becase a query is
updatable, it doesn't mean the query will still be updatable if you change
it. If you put the modified query (with the two new fields) into the SQL
window of the query builder, is the query updatable?

Carl Rapson
 

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

Popluating a form 3
recordsource updatable 3
You can't go to the specified record 4
Populating fields 3
Need help on how to pass calculated field from a form to another form 0
Sum of SQL Statement 1
Need Blank Form 3
How do I have a listbox in a form update a query 2

Top