DLookup to enter data to table, and how to use subform of M:N linking table to populate similar tbl

[Essel30]

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.

Also posted @ or in:
https://access-programmers.co.uk/for...d.php?t=293479

[June7]

1. Saving calculated values (in this case return from DLookup) requires code (macro or VBA).

2. You want to copy the ServiceID values from RequestServices table into BidServices table for a particular Request/Client pair? This can be done with an INSERT SELECT action query. In VBA:

DoCmd.RunCommand acCmdSaveRecord 'to make sure the last entered ServiceID record is committed to RequestServices table
CurrentDb.Excecute "INSERT INTO BidServices(RequestID, ClientID, ServiceID) SELECT " & Me.cbxRequestID & " AS RID, " & Me.tbxClientID & " AS CID, ServiceID FROM RequestServices WHERE RequestID = " & Me.cbxRequestID & " AND ClientID = " & Me.tbxClientID

The real trick is figuring out what event to put code into. If you want user to click a button, then probably need code to make sure the data isn't enter twice if user accidentally hits the button again or set up compound index in table to prevent duplicate combinations.

Advise no spaces or punctuation/special characters (underscore only exception) in naming convention. Underscores would be better than hyphens.

[Bob Fitz]

Cross posted: https://access-programmers.co.uk/for...d.php?t=293479


Please read: http://www.excelguru.ca/content.php?184

[Essel30]

The RequestID is not a foreign key in the Bid_Services linking table, but it is a foreign key in the Services (event) table.

The event I'm thinking is the BeforeUpdate or AfterUpdate of the combobox. I believe the way in which my primary keys are set will not allow duplicate values. Advice well noted for naming conventions.

Could you write the code and/or macro I would need to use to make #1 work?

Thanks

[June7]

Simply: Me!fieldname = Me.controlname

Again, real trick is figuring out what event to use.

[Essel30]

Thanks, I was able to use that to make my Client ID fields populate in my Bids table when I press the right arrow to make a new Bid. But I still can't find which event it is, which will update that field after selection of the combobox that selects the Request.

I also have a subform on my Bid Form that used to show the corresponding Client information based on the selection of the Request combobox, but it doesn't update anymore.

[June7]

Try the AfterUpdate event of the combobox.

How did you cause the subform to change based on the combobox? Master/Child Link properties of the subform container?

[Essel30]

I was able to solve my #1 dilemma thanks to your help by putting code:
Me![Client ID#] = Me!SelectedRequest.Column(3) after making it so that the combobox displays a hidden 4th column of the ClientID of the Request. I chose all events related to the combobox such as beforeupdate and afterupdate and onclick and onchange.

I was also able to fix the display of the subform to show the correct Client.

Now onto the hard part about making it so the Bid-Services populate the same Service ID as in the corresponding Request-Service.
Thanks so much for your help thus far. I believe I can work with your initial answer to try and solve my #2.

[Essel30]

Yes, so my Client ID has the control source of the Bid Table.
And the Client ID of the subform is linked to that.

The reason why it stopped working was because I mistakenly set it up so that the Client ID of the subform calls upon the Client ID of the Request form.

Everything was made simple by getting values through the combo box without having to deal with DLookup.

[Essel30]

DoCmd.RunCommand acCmdSaveRecord 'to make sure the last entered ServiceID record is committed to RequestServices table
CurrentDb.Excecute "INSERT INTO BidServices(RequestID, ClientID, ServiceID) SELECT " & Me.cbxRequestID & " AS RID, " & Me.tbxClientID & " AS CID, ServiceID FROM RequestServices WHERE RequestID = " & Me.cbxRequestID & " AND ClientID = " & Me.tbxClientID

Would you mind trying to changing this code if I'm trying to populate my Bid-Services subform, which sits on my Bid Form, and thus will populate my Bid-Services table where:
After selection of the combobox of RequestID located on my Bid Form, the [Bid_ID#] and [Service_ID#] of my Bid-Services table will get populated based on the Request-Services table.
My Request-Services table only have [Request_ID#] and [Service_ID#] fields, both being foreign keys and together making the primary key.

So, for example, my requestID#0001 has three service ID's: #1000, #1100, #1200. So when I select Request_ID# 0001 from the combobox of the Bid Form, then the Bid-Services linking table controlling the subform will generate 3 records/lines: 1) [Bid_ID#5000],[Service_ID#1000]; 2) [Bid_ID#5000],[Service_ID#1100]; 3) [Bid_ID#5000],[Service_ID#1200]

For each record, there will be blank spaces for the user to input [Estimated Days], [Negotiated Price].


I'm having trouble fixing the code you sent here, because my linking tables don't have the clientID fields. The use of quotes also confuse me like crazy. All of my returned values will be numbers, no strings involved, although I did format the ID# textboxes in the forms to start with a string such as "Bid-ID#" or "Req-ID#"

[June7]

If ClientID is not in the Bid-Services or Request-Services tables then don't include field in the query.

The quotes are defining the literal text to concatenate with the variable elements to construct the full SQL statement.

How did you 'format' textboxes to display those prefixes? Why don't you just have labels to show those?

Post your attempted code for analysis if you still have issues.

Still recommend not use punctuation/special characters in naming convention.

[Essel30]

Ah, that's a good idea about formatting the Labels LOL. However, I was instructed in my course to format the textboxes by inputting the format option under properties to be: "Bid-ID#"0000
It appears that the values still get stored as numbers, not as strings.

The best I can come up with is something like this:

Private Sub SelectedRequest_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
CurrentDb.Excecute "INSERT INTO Bid-Services([Bid_ID#], [Service_ID#]) SELECT " & Me![Bid_ID#] & " AS [Bid ID#] " & Forms.FormBid.subfrmRequest-Services.form.[Service ID#] & " AS [Service ID#] FROM Request-Services WHERE [Request_ID#] = " & Me.SelectedRequest & " AND [Service_ID#] = " & ???
End Sub

Not sure what to put there, or if the syntax is even ok to that point.

I don't want to mess with naming conventions at this point as I would have to tinker too many things to keep things functioning, but your point is well noted and agreed.

[June7]

I am confused. Don't you want to copy into Bid-Services table records from Request-Services table for all Service_ID# values that are associated with a particular Request_ID#?

Is the BID_ID# supposed to be the same value as Request_ID#? If Bid_ID# value has to be independently generated, this will get more complicated.

You should name combobox something like cbxReqID.

CurrentDb.Excecute "INSERT INTO [Bid-Services] ([Bid_ID#], [Service_ID#]) SELECT " & Me.cbxReqID & " AS ID, [Service_ID#] FROM [Request-Services] WHERE [Request_ID#] = " & Me.cbxReqID

I expect those hyphens and # characters in names will cause you headaches. If using, remember to always enclose names in [].

[Essel30]

Wow! That is soo close to what I need; it helps me tremendously as far is learning the syntax for that command.

I am confused. Don't you want to copy into Bid-Services table records from Request-Services table for all Service_ID# values that are associated with a particular Request_ID#?

Yes, but the Bid_ID#'s are independent from the Request_ID#'s. Because, a particular bid for Request #0005 can be made before Request #0004.
I guess things would be easier if my Bid_ID# is not an autonumber, but I do believe I need to keep it an autonumber as it represents an event.

So perhaps I will try what you just put but put Me.Bid_ID# instead of Me.cbxReqID.

Does the code change at all if it needs to insert multiple records into the subform/Bid-Services table?
You are amazing. Thank you so much.

[Essel30]

I tried adjusting the code to:
CurrentDb.Execute "INSERT INTO [Bid-Services] ([Bid ID#], [Service ID#]) SELECT " & Me.[Bid ID#] & " AS [Bid ID#], [Service ID#] FROM [Request-Services] WHERE [Request ID#] = " & Me.[SelectedRequest.Column(0)]
But it brings up a Runtime error 2465: Microsoft Access can't find the field '|1' referenced in your expression.

Here is a table representation of what I'm going for:
Every Request, and thus Bid can have varying counts of Service ID's, which makes this tricky.

Request-Services table:

Request ID#	Service ID#
00001	1000
00001	1100
00001	1200

Bid-Services table: For each record, there will be blank spaces for the user to input [Estimated Days], [Negotiated Price].

Bid ID#	Service ID#	Estimated Days	Negotiated Price
5000	1000	(blank)	(blank)
5000	1100	(blank)	(blank)
5000	1200	(blank)	(blank)

Main Bid Table:

Bid ID#	Request ID#	Employee ID#	Date	Client ID#
5000	00001	xxx	xx/xx/xxxx	xxxx