Coding Drop down box to copy information

[VVSD]

Hello every one, Im attempting to creat a database for a non profit veteran org. The data base is going to be used in a call center, where they take the callers information and the veterans information. However some times the caller is the veteran. I have a drop down list with values of "veteran;loved one" if veteran is selected i am trying to have the name values from the caller section on the form copied down to the veterans info section. All this is taking place on one form then when submited is entered into the database. any suggustions? i have very limited programming background so breaking it down "barney style" would be greatly appriciated. Thanks for the help and keep up the great work.

[MMcCarthy]

I think the simplest method for you would be to code in the AfterUpdate event of the dropdown box. Lets assume the dropdown box is called combo1, you will need to code something like the following ...

Private Sub combo1_AfterUpdate()
 
    If Me.combo1 = "Veteran" Then
        'I'm guessing field names here
        Me.VeteranName = Me.CallerName
        Me.VeteranAddress1 = Me.CallerAddress1
        ' and so on
    End If
 
End Sub

[VVSD]

Awesome, thank you so much for your help. i got it figured out, I'm sure ill be back soon with more questions. Thanks again have a great weekend.

VR,
Mike

[VVSD]

@msquared
sorry I am back. so what command could i use to leave the veteran fields empty if loved one is selected in the combo? thanks :)

[MMcCarthy]

@VVSD
In the example I gave you the veteran fields should only be filled if the dropdown box is set to Veteran so nothing should happen if anything else is selected.

[VVSD]

@msquared
I understand that, but I am talking about the possibility that it is seleceted, and it updates the veterans fields with the caller info, then is changed to loved one. I want it to clear the fields if loved one is selected so I used the code:

Private Sub Combo58_LostFocus()
   If Me.Combo58 = "Veteran" Then
       Me.firstname = Me.ContactFirstName
       Me.lastname = Me.ContactLastName
       Me.Veteransgender = Me.CallersGender
        Me.veteransage = Me.callersage
    Else: Me.Combo58 = "loved one"
    Me.firstname = " "
       Me.lastname = " "
       Me.Veteransgender = " "
        Me.veteransage = " "   
 
  End If
End Sub

Do you think i will have any problems with this? thanks

[MMcCarthy]

@VVSD
Sorry I misunderstood your question. The code you have posted looks fine. Is it giving you any problems?

[VVSD]

@msquared
No problems that I can find yet. My mistake, I didnt explain the situation very well. thanks for your help. :)

[VVSD]

@VVSD
Ok one more question for the day, I have the following code

Private Sub Submit_Click()
On Error GoTo Err_Submit_Click
    DoCmd.GoToRecord , , acNewRec
Exit_Submit_Click:
    Exit Sub
Err_Submit_Click:
    MsgBox Err.Description
    Resume Exit_Submit_Click    
    DoCmd.Close acForm, Me.Name, acSavePrompt    
End Sub

for my submit button on my client intake form. I am trying to make the submit button create a new client, then open a new form called openclients with the clients information that was just entered. any idea?

[MMcCarthy]

@VVSD
Not quite sure I know what you mean. This code just goes to the "New Record" for a form. What form is this on as opposed to what form are you then trying to open? And what is the record source for both forms?

[VVSD]

@msquared
i have a table named clients, which stores all the clients information. The current form is called clients, after entering their information i hit submit, and it enters the data from the form into the table. I want it to also open up a new form named "open clients" with the information i just entered

[MMcCarthy]

@VVSD
Is the ClientID an automated number field?

If so you could do something like this ...

Option Compare Database
Option Explicit
 
Dim clientNum As Long
 
 
Private Sub Submit_Click()
On Error GoTo Err_Submit_Click
 
    DoCmd.GoToRecord , , acNewRec
    clientNum = Me.ClientID
 
Exit_Submit_Click:
    Exit Sub
Err_Submit_Click:
    MsgBox Err.Description
    Resume Exit_Submit_Click
    DoCmd.Close acForm, Me.Name, acSavePrompt
End Sub
 
Private Sub Form_Close()
 
    DoCmd.OpenForm "Open Clients", acNormal, , "[ClientID]=" & clientNum
 
End Sub

I've used the form close event to trigger the opening of the other form but you can change that to some other event if you feel its better.