Tough form issue that uses DLookup's

[Solola]

Hi-

I have a form into which a user enters some key info, and I have a series of queries that use the data-entered values from the form to filter the options from many different tables (not the form's source), ultimately returning one row of data in the final query. I (currently) have dlookups that look to these queries and display the key data on the form's screen. I ultimately want these query fields to post in my form's table source (which essentially a point-in-time data wharehouse of data that constantly changes). However, the control source of the text field displaying the dlookup IS the dlookup formula - so how do I write this value to the table? My form's record source table does have a field into which I want to put this value.

I fear I also am making this more complicated than it needs to be...



Thank you!

[RuralGuy]

We may need just a little more description of your form to give a useful answer.

[Solola]

I'm using Access 2007, and I know just enough about VBA to be dangerous, but not necessarily comfortable.

My form's called tfrmTDMEntry, and its Recordsource is t_tiered_dnr_mgmt. This table/form is a combination of newly entered info and point-in-time existing detail for a record that already exists in our system tables.

The form is primarily data entry, but I use combo boxes with queries as their record sources to validate the data as the user enters it. For example, the user will data-enter recip_id, source_id, and request_dte. But I don't want them to be able to enter IDs that do not exist or are not associated with each other, or dates that are not associated with the recip-source combo. Hence the combo boxes which query a bunch of system tables (ODBC connections). First they enter recip_id, and the combo box queries a t_recip table. Then, I have another query that returns only source_ids that are associated with the already-entered recip_id. Then finally, a third query and combo box returns request_dtes that are associated only with the entered recip_id and source_id, for specific request types only.

Once these three fields are populated, there are 11 fields that I want to automatically populate in my table (there is only one unique record with that combination). When I originally posted this, I used a bunch of dLookups in text boxes to pull the add'l fields, but have since changed that as the recordsource of the textbox was the dlookup formula, and I couldn't figure out how to actually write that result to my underlying table. I changed the multiple text boxes to a single combo box called request_id (based on query back to the same system tables, with criteria from the 3 fields on my form). Then I wrote an AfterUpdate VBA code on that combo box in an attempt to update the fields in my form's table with the results of the combo box:

Private Sub request_id_AfterUpdate()
Me.cust_id = Me.request_id.Column(2)
Me.request_detail_id = Me.request_id.Column(3)
Me.item_id = Me.request_id.Column(4)
Me.appt_dte = Me.request_id.Column(5)
Me.cancel_dte = Me.request_id.Column(6)
Me.cancel_reason_entity_id = Me.request_id.Column(7)
Me.workup_cancel_reason_id = Me.request_id.Column(8)
Me.supplier_id = Me.request_id.Column(9)
Me.supplier_grp_id = Me.request_id.Column(10)
Me.wu_complete_dte = Me.request_id.Column(11)
End Sub

The combo box returns the correct values, but when I select it, I get a run-time error '2113': "The value you entered isn't valid for this field." And the cancel_dte row of my code above is highlighted in debug mode. I'm guessing this might have something to do with date formatting? But, when I look at the DDL for both my form's recordsource table and the table from which cancel_dte is pulled, they are both datetime.

So, I'm unable to finish the process to know if my switch to a combo box to populate my recordsource will work...

[Solola]

Well, I got the date thing to work out - apparently there's a column (0) in a combo box? So all my columns were off by one. After making that fix, it appears to have worked (no errors after updating the combo), but I have another error, which I believe can only be addressed internally with my table relationships. (I get an ODBC insert error that the parent does not exist on another system table, so I can't create a child in my table.)

Related question, though, I have different code that enables/disables an option group based on the contents of some date fields - it needs to compare one of the system-derived dates to a user-entry date. But, if I don't store the system date anywhere on my form (appt_dte), how can I refer to it?