Hi and thank you again.
frmAtt has bound properties from qryAtt which is from tblAtt. The field in
question "TxtAir" unbound is only to be displayed from qryTrv which is from
tblTrv and frmTrv. frmTrv is bound to qrtTrv which is from tblTrv. So
Airfare is stored within tblTrv. Airfare is edited/inputted by the user.
When closing frmTrv there is a couple of fields I want to display on frmAtt,
but right now (just to keep it simple) we are dealing with just one (txtAir)
and I will change the others depending on what we do for txtair because they
are of the same nature. this VBA code shows the SSN linking to frmTrv:
Private Sub btnTravel_Click()
On Error GoTo Err_btnTravel_Click
Dim DocName As String
DocName = "frmTravel"
DoCmd.OpenForm DocName, , , "[SSN]='" & Me.[SSN] & "' AND [Meet_Num]='"
& Me.[MEET_NUM] & "'", , , Me.[SSN] & "$" & Me.[MEET_NUM]
Exit_btnTravel_Click:
Exit Sub
Err_btnTravel_Click:
MsgBox Error$
Resume Exit_btnTravel_Click
End Sub
This is what happens when loading frmTrv:
Private Sub Form_Load()
If Me.NewRecord Then
Me.[SSN] = split(Me.OpenArgs, "$")(0)
Me.[MEET_NUM] = split(Me.OpenArgs, "$")(1)
Forms!frmTravel!DateProcessed = date
Forms!frmTravel!PerMil = 0.485
Forms!frmTravel!Lab1 = "Phone:"
Forms!frmTravel!Lab2 = "Internet:"
Forms!frmTravel!Lab3 = "Shuttle:"
Forms!frmTravel!Lab4 = "Metro:"
Forms!frmTravel!Lab5 = "Tips:"
Forms!frmTravel!Lab6 = "Tolls:"
End If
If IsNull([PerMil]) Then
Forms!frmTravel!PerMil = 0.485
End If
If IsNull([Lab1]) Then
Forms!frmTravel!Lab1 = "Phone:"
End If
If IsNull([Lab2]) Then
Forms!frmTravel!Lab2 = "Internet:"
End If
If IsNull([Lab3]) Then
Forms!frmTravel!Lab3 = "Shuttle:"
End If
If IsNull([Lab4]) Then
Forms!frmTravel!Lab4 = "Metro:"
End If
If IsNull([Lab5]) Then
Forms!frmTravel!Lab5 = "Tips:"
End If
If IsNull([Lab6]) Then
Forms!frmTravel!Lab6 = "Tolls:"
End If
[Last_Name] = Forms!frmAttendanceMeeting!Last_Name
End Sub
This is what happens when the button close is clicked on:
Private Sub btnCloseForm_Click()
On Error GoTo Err_btnCloseForm_Click
If Me.NewRecord Then
If Len(Me.[Order_Num] & "") = 0 Then
MsgBox "Record has not been entered - No Order Number"
End If
End If
lngMyKey = Me.txtSSN
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[ssn] = '" & lngMyKey & "'"
MsgBox Me!SSN
If rst.NoMatch Then
MsgBox "Error Find Record " & lngMyKey
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
DoCmd.Close
Exit_btnCloseForm_Click:
Exit Sub
Err_btnCloseForm_Click:
MsgBox Error$
Resume Exit_btnCloseForm_Click
End Sub
I hope this is enough information. And thank you for helping.
Andy Hull said:
Hi again
Need more info!!
So far...
frmAtt bound controls get info from where? table? query?
frmAtt unbound txtAir gets info from qryTravel - OK
frmTrv then opens and gets info from where? table? query?
and it displays different AirFares? or do you edit them?
On closing you want one of these or all of these to be shown in frmAtt?
So, is frmTrv bound to a table so edits are saved in this table?
Or, if it just displays new fares do you save them anywhere?
I would have thought that they need to be saved to the table that is used by
qryTravel so that frmAtt can pick them up - in which case, your dlookup as
stated in your original post will work.
If you show several rows on frmAtt then the "new" fares need to be saved
somewhere along with their corresponding SSN so frmAtt can put the right fare
next to the right SSN.
Hope this helps
If you need to post back, tell us more about each table, query & form
Regards
Andy Hull
Amour said:
I ran the msg and it came up with another ssn. The problem is that the
frmAtt has Nav buttons and has several records per meeting. So I quess that
I can not use an open event (or load)...
Please help and thank you!
Andy Hull said:
Is there a value for the required SSN?
Add an extra line, temporarily, to test...
Private Sub Form_Open(Cancel As Integer)
MsgBox Me.SSN
Me.TxtAir = DLookup("[Airfare]", "qryTravel", "[SSN] = '" & Me.SSN & "'")
End Sub
This will tell us the SSN it is using to filter the query.
Then you can run the query by hand to check it exists and see what the
corresponding AirFare value is.
Andy Hull
:
Thank you again for your help.
I am still having the same problem:
Private Sub Form_Open(Cancel As Integer)
Me.TxtAir = DLookup("[Airfare]", "qryTravel", "[SSN] = '" & Me.SSN & "'")
End Sub
This is what I have TxtAir = Textbox name
It displays blank when I bring the form up yet I checked the table and there
is a value there.
Please help and thank you!
:
Hi
Yes, use the name shown on the "other" tab of the control's property sheet.
(I just made up my own names as I didn't know yours).
So what you have should work. I assume that you also have a control named
SSN on the form frmAtt so it may be worth referring to it as Me.SSN so the
form's On Open event code should look like...
Private Sub Form_Open(Cancel As Integer)
Me.TxtAir = DLookup("[Airfare]", "qryTravel", "[SSN] = '" & Me.SSN & "'")
End Sub
Regards
Andy Hull
:
Thank You for responding. Airfare is a unbound field on the form (frmAtt).
so don't I put the text name?
Me.TxtAir = DLookup("[Airfare]", "qryTravel", "[SSN] = '" & [SSN] & "'")
But when I do nothing comes up
Please help and again thank you!
:
Hi
Instead of setting the air fare on form frmATT by setting its control source
- set it when you open the form.
So in the forms On Open event use...
Me.AirFare =DLookUp("[Airfare]","qryTravel","[SSN] = '" & [SSN] & "'")
Then, in the On Close event of frmTRv use...
[Forms]![frmAtt].AirFare = Me.AirFare
(Note: I've had to make assumptions about the names of your controls)
Regards
Andy Hull
:
Hi I am new to Access.
I have a Single Form (frmAtt) that has an unbound field and within the
control source I have:
=DLookUp("[Airfare]","qryTravel","[SSN] = '" & [SSN] & "'")
That same form when a button is clicked on it opens a (form frmTrv) that has
the information for Airfare.
The problem is that frmAtt takes the Airfare value from the table and not
from frmTrv. I want is information to be from the form (frmTrv). When you
close form (frmTrv) I want the new value to be displayed on frmAtt. Right
now to see changes you have to close frmAtt and then reopen it.
Please Help and thank you!
