I have a database modeling a service company who takes order requests and prepares an estimate. I've created a form that is used to document requests, and the inputs on this form will populate fields such as [RequestID], [Request Date], [Client ID], [Employee ID] in the Request event table. This Request event table is linked to a Resource table [Services] in a many-to-many relationship, with an accompanying [Request-Services] table. A second event table, [Bid] has the RequestID as a foreign key.
Both tables have a field for [Client ID], which is the foreign key connected to the [Client ID] primary key of a Client maintenance table.
I know this may go against normalization rules, where ClientID is in both the Request and Bid event tables, but I believe I have to stick with this design in order to have consistency with my REA model where the Client is connected to both events.
In my form to create a Bid, I use a combobox where the user chooses from a selection of request ID's. This will prompt a subform to display the corresponding Client ID of the selected request.
1) What I am seeking help with is how to make it so that the displayed Client ID that is returned on the Bid Form via dlookup be added to the [Client ID] fields of the Bid Table. I tried creating a textbox that takes on the value of the returned Client ID, but it only seems to become another DLookup expression. I haven't been able to find how to do so after hours of searching online.
2) My Request Form uses a subform in order to populate my Request-Services linking table with Foreign keys RequestID and ServiceID serving together as the primary key for the Request-Services table.
I'd like to be able to call upon this subform in my Bid Form after the RequestID is selected via the combobox, but I need these ServiceID values to be stored in my Bid-Services linking table. The Bid-Services linking table uses the Foreign Keys BidID and ServiceID together as its Primary Key. I believe I can get the Request-Services subform to show, but it doesn't help me be able to store those values in the Bid-Services table. The Bid-Services table also contain an [Estimated Days] field, which I plan to multiply with a [Daily Rate] of the Services table to be able to give an estimate for each row of the subform. This is very complicated for me, because the number of rows of BidID and ServiceID varies depending on which services were Requested in the Request Form.
The simple way would be for me to create a subform on the Bid form similar to how it is on the Request form, but this would mean that I, or the user, has to input the Services again how he/she already did on the Request Form which seems redundant.
I've done everything up to now using mainly system guided wizards, as I'm a novice in MS Access. I have not needed to do any VBA coding.
Thanks in advance for your help.
Both tables have a field for [Client ID], which is the foreign key connected to the [Client ID] primary key of a Client maintenance table.
I know this may go against normalization rules, where ClientID is in both the Request and Bid event tables, but I believe I have to stick with this design in order to have consistency with my REA model where the Client is connected to both events.
In my form to create a Bid, I use a combobox where the user chooses from a selection of request ID's. This will prompt a subform to display the corresponding Client ID of the selected request.
1) What I am seeking help with is how to make it so that the displayed Client ID that is returned on the Bid Form via dlookup be added to the [Client ID] fields of the Bid Table. I tried creating a textbox that takes on the value of the returned Client ID, but it only seems to become another DLookup expression. I haven't been able to find how to do so after hours of searching online.
2) My Request Form uses a subform in order to populate my Request-Services linking table with Foreign keys RequestID and ServiceID serving together as the primary key for the Request-Services table.
I'd like to be able to call upon this subform in my Bid Form after the RequestID is selected via the combobox, but I need these ServiceID values to be stored in my Bid-Services linking table. The Bid-Services linking table uses the Foreign Keys BidID and ServiceID together as its Primary Key. I believe I can get the Request-Services subform to show, but it doesn't help me be able to store those values in the Bid-Services table. The Bid-Services table also contain an [Estimated Days] field, which I plan to multiply with a [Daily Rate] of the Services table to be able to give an estimate for each row of the subform. This is very complicated for me, because the number of rows of BidID and ServiceID varies depending on which services were Requested in the Request Form.
The simple way would be for me to create a subform on the Bid form similar to how it is on the Request form, but this would mean that I, or the user, has to input the Services again how he/she already did on the Request Form which seems redundant.
I've done everything up to now using mainly system guided wizards, as I'm a novice in MS Access. I have not needed to do any VBA coding.
Thanks in advance for your help.
