advancing to new record

[buckwheat]

My form has two fields bound to a table. The first field is a CPT cbo and the second is a Dx cbo. The physician will select a CPT and it's respective Dx. He may use the same CPT for several Dx codes for the same patient and visit. Is there a way to customize the code below that would enter the CPT and Dx, but leave the selected CPT in the cbo so the doctor doesn't have to select it over and over again?


Private Sub cmdEnter_Click()
On Error GoTo Err_cmdEnter_Click

DoCmd.GoToRecord , , acNewRec

Exit_cmdEnter_Click:


Exit Sub
Err_cmdEnter_Click:
MsgBox Err.Description
Resume Exit_cmdEnter_Click

End Sub

[RuralGuy]

Set the Default value of the CBO in the AfterUpdate event of the CBO.

[buckwheat]

Not sure what you mean, can you give a little more detail please?

[RuralGuy]

Similar to this link: http://access.mvps.org/access/forms/frm0012.htm

[buckwheat]

Now I'm really lost. You mentioned to put the value at the After event of the cbo, the attached link references putting it behind a command button, so do I modify the add record code or am I putting to the cbo after event?

[RuralGuy]

You put the referenced code in the AfterUpdate event of your ComboBox.

[buckwheat]

ok, here's the problem as I see it if I put it in the cbo after event. There is going to be a button to enter another dx for a selected CPT . . . I get it that this code will carry the CPT to the next dx. But there will also need to be a next record button to advance to a completely new record . . . with the after event in the cbo it won't clear the cpt when it needs to. . . . so:

1 button will end the cpt for the next cpt (the code above); the next button will need to be programmed to keep the current CPT until the next cpt button is selected.

[buckwheat]

any takers please?

[RuralGuy]

The user can override any default value.

[Dal Jeanis]

buckwheat - Assuming that the CPT and Dx records are fields on the same table -- and you said table, singular -- Ruralguy has given you the correct answer for your app, and the behavior you have asked for.

You could provide a "clear CPT default" button on the form, but you don't need to. Your cmdEnter button code is already advancing to a new, completely blank record, and the default CPT will only become "real" when the Doc enters some data either in that field or another. Before that point, the new record doesn't actually exist with any data on it.

So, the Doc can always change the CPT number to whatever she wants, either before or after entering the new Dx. (Exception - if the Dx combo box is loaded based on the value of the CPT, then cbo selections must be in hte correct order, obviously.

[buckwheat]

Hi,

I'm certain Ruralguy's instructions were correct and I appreciate the time, but since my initial request was for code , I would have no idea what reference code or default code to put in the after event. For the link, how much of the code gets applied? I'm on the site because I am not an expert, so layman's terms is very helpful.

Regards
Buck

buckwheat - Assuming that the CPT and Dx records are fields on the same table -- and you said table, singular -- Ruralguy has given you the correct answer for your app, and the behavior you have asked for.

You could provide a "clear CPT default" button on the form, but you don't need to. Your cmdEnter button code is already advancing to a new, completely blank record, and the default CPT will only become "real" when the Doc enters some data either in that field or another. Before that point, the new record doesn't actually exist with any data on it.

So, the Doc can always change the CPT number to whatever she wants, either before or after entering the new Dx. (Exception - if the Dx combo box is loaded based on the value of the CPT, then cbo selections must be in hte correct order, obviously.

[June7]

The example in tutorial suggested button Click event to offer user option to retain the entered value as default for subsequent records. I recommend using either the BeforeUpdate or AfterUpdate event of combobox. You have code from the link, adapt it for your db, use your control name. The code can be even simpler, variable not needed, assumes data is text and not number or date.

Me.comboboxname.DefaultValue = """" & Me.comboboxname & """"

Do you know how to create the VBA procedure? In the event property, select [Event Procedure], click the ellipses (...) to open the VBA editor, type code into the procedure.

Now moving to a new record is a different issue. This can be automatic after entering or tabbing through the last control on form. This is natural behavior of form and no code is needed.

[Dal Jeanis]

buckwheat - Sorry, I should have gone down a level in description of the procedure, as June7 has done. Please let us know if you need any more help on this one. IF not, please mark the thread solved (top of page, under Thread Tools).

Here's the code that would go behind the AfterUpdate event of the CPT combo box, which I've called cbxCPT for the sake of the example.

Private Sub cbxCPT_AfterUpdate()
On Error GoTo Err_cbxCPT_AfterUpdate

Me.cbxCPT.DefaultValue = """" & Me.cbxCPT & """"

Exit_cbxCPT_AfterUpdate:
    Exit Sub

Err_cbxCPT_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_cbxCPT_AfterUpdate
    
End Sub

Here's the only code that is absolutely necessary - the rest of the above code is the normal practice for error trapping VBA code, and is probably overkill for this particular event.

Private Sub cbxCPT_AfterUpdate()

Me.cbxCPT.DefaultValue = """" & Me.cbxCPT & """"

End Sub

[buckwheat]

Hi,

Yes, I can put VBA code to events . . . sometimes gets tricky reasoning which event to use.

I'm guilty as well for assuming everyone knew what I was doing with my form, so, I apologize for not explaining better. Here goes:

My main form has a cbo (which will be changed to a list box later). Users select a patient's name and demographics for that patient populate in a subform. The CPT and Dx cbo are in another subform. These are the only two fields the doctor needs to populate, demographics, CPT and Dx control source is a master table to generate reports.

So, when I put the code in the CPT box, I can see it carries to the next record, BUT it also carries to the each patient selected from the main patient cbo box . . . it needs to clear out when a new name is selected; this is why I thought I needed code.

Thanks for your patience.

The example in tutorial suggested button Click event to offer user option to retain the entered value as default for subsequent records. I recommend using either the BeforeUpdate or AfterUpdate event of combobox. You have code from the link, adapt it for your db, use your control name. The code can be even simpler, variable not needed, assumes data is text and not number or date.

Me.comboboxname.DefaultValue = """" & Me.comboboxname & """"

Do you know how to create the VBA procedure? In the event property, select [Event Procedure], click the ellipses (...) to open the VBA editor, type code into the procedure.

Now moving to a new record is a different issue. This can be automatic after entering or tabbing through the last control on form. This is natural behavior of form and no code is needed.

[buckwheat]

Hi Dal,

No need to apologize. Thanks for the code. Can this be modified to accommodate the condition outlined in my last thread?