Combo box tied to data entry

[jondavidf]

Hey all,

I've created a form to be used for data entry that opens to a blank (new) record. I'm trying to create a combo box (Study ID on left) that when a value from the drop-down is selected the user can enter data to create a new "Encounter".

What my combo box does so far:

1 - Passes the value (from another table) to the Study_ID field on the right side of the form (which will later be hidden).

2 - Refreshes the subform query to show previous "encounters".

Problems I'm having:



1 - Instead of adding a new record the combo box brings up the last record and tries to modify that one. Whenever I set the combo box to also "GoTo Record -> New" it also clears the value from both Study_ID fields, defeating the purpose of passing the value to Study_ID on the right.

2- Also it seems to add entries to the associated table without me intentionally doing so (see the blank record in the query). I'd like the record to remain unsaved until I select the command box "Save Encounter".

Any help is tremendously appreciated.

Thanks!!

[June7]

Record is committed to table when: 1. close table/query/form or 2. move to another record or 3. run code to save.

In a linked form/subform arrangement, moving to the subform commits the parent record.

After entering the BMI, focus goes to new record therefore the record just entered is committed. Then when you click the button, another record (with zeros) is committed.

The combobox is UNBOUND? Controls used to input search/filter criteria should be UNBOUND.

Values are cleared with "GoTo Record -> New" because you move to a new record row. Why would you expect otherwise?

[jondavidf]

Thanks June! I believe everything is now functioning properly. In case anyone else is interested or has any suggestions for improvement I did the following:

1 - Removed the action to pass the combo box to Study_ID.

2 - Set macro for combo box 'On Click' event to GoToRecord -> New, and RunMenuCommand -> Refresh.

3 - Set property of qryPrevEncounters (which is the subform) Study_ID field to Criteria -> [Forms]![frmEncounters]![boxStudyID].

Now whenever a new value is selected from the combo box previous encounters for that ID is shown on the subform query, yet the values in the main form remain blank except the combo box which remains at the value the user selected.

4 - Set macro of cmdSaveEncounter 'On Click' event to

a. SetProperty of Study_ID from Value =[boxStudyID]
b. RunMenuCommand -> SaveRecord
c. RunMenuCommand -> Refresh
d. GoToRecord -> New

5 - I also added a delete query to delete any records from the tblEncounters without a date and coded the cmdSaveEncounter to run this after it ran the commands above. That way, if a user clicked "Save Encounter" without a date it would technically save but then delete that record.

Now, any time the combo box is changed you can see all previous encounters for that Study ID but with a blank record displayed on the main form. A new record will not be added until the user enters data in the main form. Also BMI is calculated based off height and weight in the qryPrevEncounters.

I know this is probably not the best way to set up the macros and all, but with my limited coding ability it's what I came up with and seems to work.