relationship for creating a data entry form

[BittenApple]

I forgot to say the year is not a date, only it a text. Regards

[HansV]

Do the rates depend on the hospital and year only, or do they depend on the hospital, department and year?

[BittenApple]

Hello Hans,
I didn't go by your response right away and I just wanted to find a work around. As long as there was a response, I was confident that at end of day, everything would be fine.
I watched videos and reach some passages from office support.
I did the form with two combo boxes laying on, it worked and then I made two combo boxes with one subform, my alternate solution was that I wanted to use to foreign key hosID and DepID concatenated in parent and child relation property window; can we concatenate two pks in property window for parent and child property? The reason I started doing it was the composite key was giving me error, yesterday.
I linked the dep table to trans table by depID and then hos table to trans table by hosID; when I use only one single key, the combo box is linked to subform, but I don't know how to use the combination of two keys in parent and child space to link both combo boxes to a subform with using two pks instead of one composite key.

==============================
I made a query for those static items for argu or rate table, the query came back with 3 rows, one for each year. I inserted textboxes on the form and with a DLookUp function, I was able to pull values for the year 2013, but I don't know how to pull the second row of the query result and show those for year 2014. Perhaps looking up two items: year and hosName, is this approach right?

Is there any other way to capture the values of query in textboxes other than DLookUp?
========================
Many, many, many thanks,
biap,

[HansV]

You could create a list box that displays all entries for a hospital, or another subform.

If you want to keep on using DLookup, the WhereCondition argument should filter on hospital and year.

[BittenApple]

Hello Hans,



1-I want to know if I can use two pks from two tables in property sheet shown in the attachment:
2-What other options do I have if I don't want to use DLookUp?

Thanks,
bitap

[HansV]

Your document contains a tiny mirrored image. Here it is in readable form:

S1461.png

You can link a subform to its main form by multiple fields; the fields must be in the same order and of the same type. For example:

Link Master Fields: ThisField;ThatField
Link Child Fields: ThisField;ThatField

As you see, the field names are separated by semi-colons.

Another way to populate controls is by using VBA code.

[BittenApple]

Hello Hans,
Thank you for enlarging the image.

Another way to populate controls is by using VBA code:
Is the code complicated?
Can this code do the job? Don't you think I should stick to DLookUp is better?
I found this code: Private Sub cbo3_Change()
Me.tbx2 = ("SELECT tbl_Billing.Savings_b FROM tbl_Billing GROUP BY tbl_Billing.UBI_b, tbl_Billing.TaxYr_b, tbl_Billing.TaxPrg_b, tbl_Billing.Savings_b HAVING (((tbl_Billing.UBI_b)=forms!f1_UpBilled!cbo1) And ((tbl_Billing.TaxYr_b)=forms!f1_Upbilled!cbo2) And ((tbl_Billing.TaxPrg_b)=forms!f1_UpBilled!cbo3));")
End Sub

Regards,
apbit,

[HansV]

If there is more than one record that satisfies the condition in the SELECT statement, the code would fill the text box with each of the values of the field s1, but in the end you would only see the value of s1 in the last matching record...

[BittenApple]

Hans,
Then the code on the top is not going to meet my requirement.
If I want to populate 30 text boxes for years 2013, 2014 and 2015, 10 for each, What is the level of complexity of the code?
If it is not too complicated, can you please let me know what I can go by?
I have this code:
Private Sub Command7_Click()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM dbo_MemberMain WHERE [MemberNo] = Me.[memberNo]")
Me.[Forename] = rs!Forename
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
I am pretty sure that I have to do so many changes to the code if essentially this is the right code.
This is the Scenario:
when the value of the combo box changes, the values of a query for these 3 years are 3 records fills out the controls on the form.
Regards,
baitap

[HansV]

I asked earlier whether the rates depend on the hospital and year only, or do they depend on the hospital, department and year? You haven't answered that yet.

[BittenApple]

Hans,
I am so sorry for not answering your question.
The rates only depend on the hospitals and years only. They are coming from a table that is populated with values. This table gets updated on monthly basis
Regards,
apbt

[HansV]

Creating separate text boxes is too much work. I'd use a subform, linked to the main form on HosID.
See the attached version. As you move from record to record in the main form, you'll see the rates change when the hospital changes.

database.zip

[BittenApple]

Hans,
I hope you are doing well.
when I change the entries in combo boxes in your example, it gives me an error:
The changes you requested to the table were not successful because the would create duplicated values in the index, primary key, or relationship. Change the data in the fields or fields that contain duplicate data. remove the index, or redefine the in index to permit duplicated and try again.

on my Example:
When I enter my a new record in subform after changing the entries in combo boxes, the hosDepID which a fk in transTable doesn't change, stays same as 1 in all records, however, It has to change, as I see on yours, it is changing. I made my relation exactly as yours.

I didn't attend to last part yet.

Regards,
appbit,

[HansV]

You can enter "track" records for the existing hospital/department combination in the form. If you want a different combination, you must move to the appropriate record in the main form, or if that combination doesn't exist yet, create a new record in the main form. You shouldn't just change the hospital and department in the existing records.

(It would have been better to use a different form design, as in the sample database I pointed you to earlier, but you insisted on doing it this way...)

[BittenApple]

Hello Hans,
-You can enter "track" records for the existing hospital/department combination in the form. If you want a different combination, you must move to the appropriate record in the main form, or if that combination doesn't exist yet.
This is what I do: I select entries form two combo boxes and enter my items in subform, then I select two different entries from combo boxes and I start a new entry on the subform, hosDepID stays same as 1; however I selected a different entries.

For a new record should I navigate in a main form? That might be the reason for error. I don't navigate to a new record on Main form.

(It would have been better to use a different form design, as in the sample database I pointed you to earlier, but you insisted on doing it this way...) I am following your sample. I am using composite pk and I am not using hosID;DepID in child and parent relationship window if my insistency refers to this topic.

Regards,
bita

[HansV]

When you are in an existing record on the main form, you should NOT select a different hospital or different department from the combo boxes. The records that you see in the "track" subform belong to that specific combination of hospital and department. Move to a different record in the main form to view, edit or enter "track" records for a different combination of hospital and department.

[BittenApple]

Hans,
Yes, I did and it worked and I think I was doing the data entry wrong by staying on the same record but expecting HosDepId to change.
Thankkkkks and many, many more,
Since you are saying that I have insisted on, I am not sure to which part you are referring to; and I am not 100 percent sure If this style is all right at all (I mean two combo boxes and one subform)
---------------------------------------------------
I am working hard to find out why your database is not working while mine is:
Whenever I select the combo in the attachment, it gives me an error
Regards,
tibp

[BittenApple]

Hello Hans,
The argu forms worked well in the beginning; I made another copy of database and on the second copy the argu form doesn't get updated when I select something from main form.
What I notice, when I go to next record and come back to previous one, I saw the form argu is updated, but it doesn't update as I select hosID from main from.
Any tips, please?
Regards,
app

[HansV]

Open the main form in design view.
Click once on the argu subform to select it.
Activate the Data tab of the Property Sheet.
Have the Link Master Fields and Link Child Fields properties been set correctly?

[BittenApple]

Hello Hans,
I did what you said. Both are set on hosID, but still the same thing, no change is made. I have been spent almost 4 or 5 hours, I have not found what is going on; while it worked perfect same time. it works but If I navigate the next record and then come back and I see that the form argu is populated. When I navigate back on the main form all argu form is populated.
what I can see the main form is place on hosDep table and then I inserted two combo boxes for hos and dep and populated them with HosID and depID from hostable and deptable.
can we say that hosID in hosDep table is blank that is why it is not pulling the data into argu form? However, it worked in the beginning.
Regards,
elap