Form/Query Help

PRodgers4284

Registered User.
Local time
Today, 02:34
Joined
Feb 17, 2009
Messages
64
I am having trouble with a form the is used to view the records in a table. The problem is that two options in the view form are displaying incorrectly, i have two combo box options (hospital and ward) in a form called "Admission" both of these are used to populate the combo boxs for hospital and ward in the form, this works fine. The problem is when I open the "viewAdmission" form, the hospital and ward options display the hospital ID and ward ID instead of the hospital name and ward name. Can anyone help me with this please.
 
to hide the first column (containing the primary key) just set the width to zero.
 
to hide the first column (containing the primary key) just set the width to zero.


Dennisk that wont work, ive tried using a query inside the form combo box (Hospital),

SELECT Hospital. HospitalID, Hospital.HospitalName
FROM Hospital
WHERE (((Hospital.HospitalID) = Hospital))
ORDER BY Hospital.HospitalName;

Im selecting from a table "Hospital" the HospitalID and HospitalName
Where the HospitalID = to the value in the "Hospital" combobox in the form.

I basically have the HospitalID for the hospital displaying in the form but i want the name of the hospital displayed in the form. I need to lookup the HospitalID for the Hospital table and match it which the Hospital name and output it in the form.
 
What's wrong with the query? It should work in the combo to show you the hospital, even though it is storing the ID. Just set the combo's Number of Columns property to 2 and the Column Width property to 0";1" (the users shouldn't probably need to be bothered with the ID).

Or if you want a text box to display the value, make sure the combo has the Number of Columns set to 2 and then in the combo's After Update event you put

Me.yourTextBoxName = Me.ComboNameHere.Column(1)

where column(1) is the second column of the combo (which is zero-based).
 
What's wrong with the query? It should work in the combo to show you the hospital, even though it is storing the ID. Just set the combo's Number of Columns property to 2 and the Column Width property to 0";1" (the users shouldn't probably need to be bothered with the ID).

Or if you want a text box to display the value, make sure the combo has the Number of Columns set to 2 and then in the combo's After Update event you put

Me.yourTextBoxName = Me.ComboNameHere.Column(1)

where column(1) is the second column of the combo (which is zero-based).


Bob i got it sort of working for the hospital name, but when i go to the next record in the database using the form the hospital name for that record is blank.
 
Bob i got it sort of working for the hospital name, but when i go to the next record in the database using the form the hospital name for that record is blank.

Call the After Update of the combo in the form's On Current event:

YourComboName_AfterUpdate

just like that
 
Call the After Update of the combo in the form's On Current event:

YourComboName_AfterUpdate

just like that


Like this you mean

Code:
Private Sub Form_Current()
Me.DateCompleted = Now()
Hospital_AfterUpdate
End Sub
 
Yep that's what I mean.

Bob it isnt working, this is my full code for the view form:

Code:
Private Sub Form_Current()
Me.DateCompleted = Now()
Me.Ward.Requery
Hospital_AfterUpdate
End Sub
 
Private Sub Form_Load()
If IsNull(Hospital) Then
    Me.Hospital = Me.Hospital.ItemData(0)
    Call Hospital_AfterUpdate
End If
 
End Sub
 
Private Sub Command861_Click()
On Error GoTo Err_Command861_Click
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_Command861_Click:
    Exit Sub
Err_Command861_Click:
    MsgBox Err.Description
    Resume Exit_Command861_Click
 
End Sub
Private Sub Command862_Click()
On Error GoTo Err_Command862_Click
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_Command862_Click:
    Exit Sub
Err_Command862_Click:
    MsgBox Err.Description
    Resume Exit_Command862_Click
 
End Sub
 
Private Sub Command863_Click()
On Error GoTo Err_Command863_Click
 
    DoCmd.GoToRecord , , acPrevious
Exit_Command863_Click:
    Exit Sub
Err_Command863_Click:
    MsgBox Err.Description
    Resume Exit_Command863_Click
 
End Sub
 
Private Sub Command864_Click()
On Error GoTo Err_Command864_Click
 
    DoCmd.GoToRecord , , acNext
Exit_Command864_Click:
    Exit Sub
Err_Command864_Click:
    MsgBox Err.Description
    Resume Exit_Command864_Click
 
End Sub
 
Private Sub Command865_Click()
On Error GoTo Err_Command865_Click
 
    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_Command865_Click:
    Exit Sub
Err_Command865_Click:
    MsgBox Err.Description
    Resume Exit_Command865_Click
 
End Sub
 
Private Sub Command866_Click()
On Error GoTo Err_Command866_Click
 
    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_Command866_Click:
    Exit Sub
Err_Command866_Click:
    MsgBox Err.Description
    Resume Exit_Command866_Click
 
End Sub
 
Private Sub Command867_Click()
On Error GoTo Err_Command867_Click
 
    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_Command867_Click:
    Exit Sub
Err_Command867_Click:
    MsgBox Err.Description
    Resume Exit_Command867_Click
 
End Sub
 
Private Sub Command868_Click()
 
 
On Error GoTo Err_Command868_Click
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_Command868_Click:
    Exit Sub
Err_Command868_Click:
    MsgBox Err.Description
    Resume Exit_Command868_Click
 
End Sub
 
Private Sub Command908_Click()
On Error GoTo Err_Command908_Click
 
    DoCmd.close
Exit_Command908_Click:
    Exit Sub
Err_Command908_Click:
    MsgBox Err.Description
    Resume Exit_Command908_Click
 
End Sub
 
Private Sub Hospital_AfterUpdate()
Me.Ward = Null
Me.Ward.Requery
Me.Ward = Me.Ward.ItemData(0)
End Sub
 
Private Sub Command938_Click()
On Error GoTo Err_Command938_Click
    Dim stDocName As String
    stDocName = "FindAdmission"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Command938_Click:
    Exit Sub
Err_Command938_Click:
    MsgBox Err.Description
    Resume Exit_Command938_Click
 
End Sub
 
Private Sub Command939_Click()
On Error GoTo Err_Command939_Click
 
    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_Command939_Click:
    Exit Sub
Err_Command939_Click:
    MsgBox Err.Description
    Resume Exit_Command939_Click
 
End Sub
 
If you use the CALL command then you need the parens, I believe. If you don't use it, just use the event without the parens. So, if you use CALL then it would be:

Call Hospital_AfterUpdate()

or just

Hospital_AfterUpdate

without the CALL.
 
If you use the CALL command then you need the parens, I believe. If you don't use it, just use the event without the parens. So, if you use CALL then it would be:

Call Hospital_AfterUpdate()

or just

Hospital_AfterUpdate

without the CALL.

Bob i have used the Hospital_AfterUpdate but im getting a compile error "Method or data member not found". The error is located at the Hospital_AfterUpdate function at ".Itemdata (0)".
 
Bob i have used the Hospital_AfterUpdate but im getting a compile error "Method or data member not found". The error is located at the Hospital_AfterUpdate function at ".Itemdata (0)".

Because you didn't use what I told you to use. I never said anything about Itemdata, did I? I said, to use Hospital.Column(1) for the name if you have the ID and the name in the query with the ID first and the name second. And the Number of columns property needs to be set to 2.
 
Bob i have it working :), the working code is as follows:

Code:
Private Sub Form_Current()
Me.DateCompleted = Now()
Me.Ward.Requery
Me.Hospital.Requery
 
End Sub
Private Sub Form_Load()
If IsNull(Hospital) Then
    Me.Hospital = Me.Hospital.ItemData(0)
    Call Hospital_AfterUpdate
End If
    
End Sub
Private Sub Command861_Click()
On Error GoTo Err_Command861_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_Command861_Click:
    Exit Sub
Err_Command861_Click:
    MsgBox Err.Description
    Resume Exit_Command861_Click
    
End Sub
Private Sub Command862_Click()
On Error GoTo Err_Command862_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_Command862_Click:
    Exit Sub
Err_Command862_Click:
    MsgBox Err.Description
    Resume Exit_Command862_Click
    
End Sub
Private Sub Command863_Click()
On Error GoTo Err_Command863_Click

    DoCmd.GoToRecord , , acPrevious
Exit_Command863_Click:
    Exit Sub
Err_Command863_Click:
    MsgBox Err.Description
    Resume Exit_Command863_Click
    
End Sub
Private Sub Command864_Click()
On Error GoTo Err_Command864_Click

    DoCmd.GoToRecord , , acNext
Exit_Command864_Click:
    Exit Sub
Err_Command864_Click:
    MsgBox Err.Description
    Resume Exit_Command864_Click
    
End Sub
Private Sub Command865_Click()
On Error GoTo Err_Command865_Click

    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_Command865_Click:
    Exit Sub
Err_Command865_Click:
    MsgBox Err.Description
    Resume Exit_Command865_Click
    
End Sub
Private Sub Command866_Click()
On Error GoTo Err_Command866_Click

    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_Command866_Click:
    Exit Sub
Err_Command866_Click:
    MsgBox Err.Description
    Resume Exit_Command866_Click
    
End Sub
Private Sub Command867_Click()
On Error GoTo Err_Command867_Click

    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_Command867_Click:
    Exit Sub
Err_Command867_Click:
    MsgBox Err.Description
    Resume Exit_Command867_Click
    
End Sub
Private Sub Command868_Click()
 

On Error GoTo Err_Command868_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_Command868_Click:
    Exit Sub
Err_Command868_Click:
    MsgBox Err.Description
    Resume Exit_Command868_Click
    
End Sub
Private Sub Command908_Click()
On Error GoTo Err_Command908_Click

    DoCmd.close
Exit_Command908_Click:
    Exit Sub
Err_Command908_Click:
    MsgBox Err.Description
    Resume Exit_Command908_Click
    
End Sub

Private Sub Command938_Click()
On Error GoTo Err_Command938_Click
    Dim stDocName As String
    stDocName = "FindAdmission"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Command938_Click:
    Exit Sub
Err_Command938_Click:
    MsgBox Err.Description
    Resume Exit_Command938_Click
    
End Sub
Private Sub Command939_Click()
On Error GoTo Err_Command939_Click

    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_Command939_Click:
    Exit Sub
Err_Command939_Click:
    MsgBox Err.Description
    Resume Exit_Command939_Click
    
End Sub
 
I don't think it will work for you though. Hospital.ItemData(0) will ALWAYS return the first item in the combo, which is not what you want.
 
I don't think it will work for you though. Hospital.ItemData(0) will ALWAYS return the first item in the combo, which is not what you want.


Bob i have everything working, the hospital and ward combo boxes for the view form is working :). I have used the following queries:

Hospital:

Code:
SELECT Hospital.HospitalName, Hospital.HospitalID
FROM Hospital
WHERE (((Hospital.HospitalID)=[Hospital]))
ORDER BY Hospital.HospitalName;

Ward:

Code:
SELECT Ward.WardName, Ward.id
FROM Ward
WHERE (((Ward.id)=[Ward]));
 

Users who are viewing this thread

Back
Top Bottom