Combo box select value

[dejanc]

Hello,
I have a Combo box with 2 values, Shipper and Receiver. How to create a query, macro or code, when I choose specific value, in my case Shipper, I want to open specific form, or something else...

thank you to everyone in advance for help

 

[wiklendt]

two ways, i prefer the second:

1) call the form in the "after update" event of the combo box; or

2) put a button on your form which uses the combobox's ID as the 'where' component of the form open. when the user chooses which value they want, they then press the button to invoke the correct form. it prevents the form just popping up if the user accidentally selects the wrong value.

 

[wiklendt]

sorry, forgot to add that both options use VBA code in the VBA editor, which you can invoke by clicking the ellipsis on the relevent event in the properties of that control.

i.e.,

 

[dejanc]

I didnt understand very well. I still dont know how to set, when user will select specific value in combo box, after will open another form.

 

[wiklendt]

what version of access are you using?

 

[dejanc]

wiklendt 2003 or 2007, and first thanks for help. Question, why which version?
I realy dont know how could I set a specific value to open somehting.

What do you thing with lookup-s?

Can you send attach a little sample?

 

[wiklendt]

wiklendt 2003 or 2007, and first thanks for help. Question, why which version?
I realy dont know how could I set a specific value to open somehting.

What do you thing with lookup-s?

Can you send attach a little sample?

because microsoft completely changed the interface and menu structure between versions 2003 and 2007. if you need step-by-step instructions, they will be very different between the two versions. just google "access 2003 interface" and "access 2007 interface" and then switch to images view. you'll see what i mean - so it's good to know which version you have. not to mention the file structure is also completely revamped (2007 file extension is .accdb, while all previous are .mdb)

version 2010 will look different too, though i have not looked into it, so i don't know if it will retain the menus of 2007.

you should find out exactly which version you have. if it's 2007, you'll easily tell b/c the menu structure is "ribbon-like" instead of the 'normal' menus of previous versions and most other software. in older versions, you'd find the version somewhere in the "help" or "tools" menu, or some other logical place. or your desktop shortcut may tell you... there are several ways of determining which version you have. a little directed effort on your part would surely find it soon enough.

as for a sample... none come to mind just off the top of my head... i'll have to think about it, search for it, or make it. not tonight. it's bed time.

 

[wiklendt]

Hello,
I have a Combo box with 2 values, Shipper and Receiver. How to create a query, macro or code, when I choose specific value, in my case Shipper, I want to open specific form, or something else...

thank you to everyone in advance for help

can you be more specific about what you actually want to open/do? that is, are you opening the SAME form but on a different record? or are you opening DIFFERENT forms depending on the value?

in case of the first, the most simple way would be to use a filter in a command button's "On Click" event:

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmYourForm"
    stLinkCriteria = "YourID = " & Me.cmbYourCombo

    DoCmd.OpenForm stDocName, , ,stLinkCriteria

edit: the above code assumes that the bound column of your combo box control is your primary key field


it gets more complicated if you want to do other things, so let me know if this is all you need, and if not, please be specific which bits you don't understand or what you actually need.

 

[dejanc]

I have attached a picture.

So when I will select Third Party value, I want to auto open a new form.

I think it should AfterUpdate event...

 

[dejanc]

I have fill this code with my datas, but all the time getting syntax error. I should change this lines, right? Please correct me, if I`m wrong.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "obrThirdParty"
stLinkCriteria = "YourID = " & Me.cmbYourCombo //I dont really what I should put in here//

DoCmd.OpenForm stDocName, , ,stLinkCriteria

 

[wiklendt]

I have fill this code with my datas, but all the time getting syntax error. I should change this lines, right? Please correct me, if I`m wrong.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "obrThirdParty"
stLinkCriteria = "YourID = " & Me.cmbYourCombo //I dont really what I should put in here//

DoCmd.OpenForm stDocName, , ,stLinkCriteria

"YourID" is the primary key ID you've given to the thing that you want to filter. for example, you may have something like:

tblType
------------
TypeID (Primary Key, Autonumber)
Type (Text)
------------

then the table in datasheet view would have two records that you have put in it:

TypeID   Type
1         Shipper
2         Receiver

but you need to put in what YOU have in YOUR database - we don't know what you have unless you tell us.

then, "Me.cmbYourComboBox" is the name you have given on the form for that control (the dropdown box). access assigns a generic name, something like "Combo14", but you should have changed it to something sensible, like "cboType".

again, this is something that only you know - we don't know what you called your combobox unless you tell us.

this combobox SHOULD have not only the text field, but also (as a 0cm width, to hide it from the user) the TypeID field. The TypeID field should be the FIRST field in the combo, and the "bound" column of the combo should be set to "1".

i think you are using a different version of access to me, but the settings should be similar.

so the bits you will need to change are:
1) the combobox properties, if you have not done it as explained
2) the code where highlighted in red:

Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "[B][COLOR=red]obrThirdParty[/COLOR][/B]"
    stLinkCriteria = "[COLOR=Red][B]YourID [/B][/COLOR]= " & Me.[COLOR=Red][B]cmbYourCombo[/B][/COLOR]

    DoCmd.OpenForm stDocName, , ,stLinkCriteria

it looks like you already have the first one ok, but you still need to put your PK (primary key) field name in, and your combobox name ("Me." just tells access that you are working with the same form that the code is put into)

HTH (hope that helps)

 

[dejanc]

I would like to ask one more thing, pleaseee...

It is the same project. I have create macro OutputTo xls format. Export file is caled ExportFile. At the moment, each time when macro is run, it overwrite the file. But I will need a history files for each day. So, how can be done that each time when the file will be exported, it will have the name, for example in my case, ExportFile1, and another export job ExportFile2, and after ExportFile3...

 

[wiklendt]

not sure how to do it in a macro, but in VBA you'd tell access the various bits of the file name for it to put together. a better way than just incrementing the number is if you use a real ID or current date (or a combination) - just for clarity.

this is what i have in one of my databases:

Private Sub cmdExportStudyData_Click()
On Error GoTo Err_cmdExportStudyData_Click

    Dim strStudy, strDate, strFileName, strExportFile, strExportFolder, strSpec, strDoc As String
    Dim strMsgTitle, strMsgInfo As String
    
    strDoc = "qryExportStudyData"
    strStudy = cmbStudies
    strDate = Format(Date, "YYYYMMMDD")
    strFileName = strStudy & " sero (" & strDate & ").txt"
    strSpec = "exqryExportStudyData"
    strExportFolder = "\ExportResults\"
    strExportPath = CurrentProject.Path & strExportFolder
    strExportFile = CurrentProject.Path & strExportFolder & strFileName
    
    strMsgTitle = "Empyema Study"
    strMsgInfo = vbInformation + vbOKOnly
    strMsgError = vbCritical + vbOKOnly

    If IsNull(cmbStudies) Then
        MsgBox "Please select a study first", vbExclamation, "Empyema Study"
        
    Else

    DoCmd.TransferText acExportDelim, strSpec, strDoc, strExportFile, False
    MsgBox "Your data file is saved as: " & Chr(13) & strFileName & Chr(13) & "This file overwrites any previous data file made today." & Chr(13) & Chr(13) & "And is saved here: " & Chr(13) & strExportFile, strMsgInfo, strMsgTitle
    
    Shell "notepad.exe " & strExportFile, vbNormalFocus
    
    End If

Exit_cmdExportStudyData_Click:
    Exit Sub

Err_cmdExportStudyData_Click:
     
    Select Case Err.Number
      Case 3044
        'destination folder does not exist, replace default warning with this:
        MsgBox "Could not write file." & Chr(13) & "Please ensure the subfolder " & strExportFolder & " exists in the database directory, then try again." & Chr(13) & Chr(13) & "Full file path expected:" & Chr(13) & strExportPath, strMsgError, strMsgTitle
      Case Else
        Msg = "Error # " & str(Err.Number) & Chr(13) & Err.Description
        MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
    End Select
   
    Resume Exit_cmdExportStudyData_Click

End Sub

note that the reason the message box says "this file overwrites any previous data file made today" because my file name only has the date appended to it, so if i make a file tomorrow, it will not overwrite yesterday's file because it has a different file name.

HTH

 

[dejanc]

uf, long code. ok, not long for application. but, long for access .

can you please mark, what should I have to change?

question?
I would like to learn VB language, but dont know how to start...do u have a suggestion?

 

[boblarson]

I would like to learn VB language, but dont know how to start...do u have a suggestion?

Since you asked - here's a good set of tutorials on VBA:

Access 2003 VBA

Access 2007 VBA

 

[dejanc]

thanks Boblarson