Open Record in New Form

[Mearntain]

I seem to be having an issue with pulling up a specific record from a combo box, and can't get it to work for the life of me. I have it working fine on a form that is searching for a numeric record, and it works as it should. However, on this form, I am trying to search for a record using a String (instead of numeric), and its not working, so I am assuming that I am doing something wrong with the string.



What I have:

I have a form with an unbound ComboBox named comboVendor. I created the combo box using the wizard so it does currently show all of the Vendors from the table tblVendor in it. I would like a user to be able to select a Vendor from the combo box, click a button, which would then prompt that record to be pulled up on a new form for the user to edit the info. Currently when the button is pressed, it brings up a blank record, and also deletes all info from the record that I am trying to pull up.

Here's the code I am using:

Option Compare Database

Private Sub btnLocateVendor_Click()


Dim VendorID As String
Dim currentwin As String


currentwin = "frmEditVendor"




VendorID = DLookup("Vendor", "tblVendor", "Vendor='" & Me.comboVendor & "'")


If VendorID = "" Then
    MsgBox ("There is not a Matching Vendor Name!")
    DoCmd.OpenForm "frmMainSwitchBoard"
    DoCmd.Close acForm, currentwin


Else
    DoCmd.OpenForm "frmEditVendor2", , , "Vendor='" & VendorID & "'"
    DoCmd.Close acForm, currentwin
End If
End Sub

I feel like this is an easy fix. I also tried it using Nz(Dlookup()) with no luck. Thanks in advance!

[Homegrownandy]

use the button wizard, (creates a macro) to open vendor where vendor ID = "yourcombobox". make sure the form is opened in edit mode.

[Mearntain]

I don't see where that is possible to have the button wizard link FIND RECORD to the combo box. I can use the wizard for "Record Navigation" to Find Next, Find Record, Go to First Record, Go To Last Record, Go To Next Record, and Go To Previous Record. None of which prompt me to connect a combobox to it, including the Find Record option.

Certainly someone can help me fix my VBA code to make this work? I feel that this should be a very easy tweak to what I have to make the code work considering that it works perfect if I edit it to search for a numeric value instead (however numeric will not what I need to search for this form).

[orange]

Since you
select a Vendor from the combo box
why not use the after update of the combo box? Do you also need a button?

Tell us more about the form and the combo.

You could post a copy of the database to get more focused responses.

Good luck with your project

[Mearntain]

I've attached a copy of the file. The form with the combobox is located in frmEditVendor and the desired location to view and edit the record is frmEditVendor2. A working version of this code (but locating a record for a numeric value instead) is located in frmMarkReceived.

I tried changing the code to AfterUpdate and it still doesn't work. The reason I chose to put a button was for the event that a user selects the wrong item in the combo box at first, they still have the option to change it and the all the button is for is to confirm their selection without having to go through multiple forms to get back to the Edit form.

[aytee111]

The reason the fields are blank in form 2 is that you set them to blank when you open the form.

[orange]

In your comboVendor, you allow a value of Null which results in an error. I would not allow user to select a null (I'd force a selection)
But there may be circumstances with your set up/business that I am not aware of or haven't understood.

I added a few lines of code that may be helpful.

Private Sub comboVendor_AfterUpdate()

    Dim VendorID As String
    Dim currentwin As String

    currentwin = "frmEditVendor"

    If IsNull(Me.comboVendor) Then
        MsgBox "You must select a valid vendor", vbOKCancel, "NoVendorSelected"
        Exit Sub
    End If


    VendorID = DLookup("Vendor", "tblVendor", "Vendor='" & Me.comboVendor & "'")

' If they must select a valid vendor, then don't allow a null option 
    If VendorID = "" Then
        MsgBox ("There is not a Matching Vendor Name!")
        DoCmd.OpenForm "frmMainSwitchBoard"
        DoCmd.Close acForm, currentwin

    Else
        DoCmd.OpenForm "frmEditVendor2", , , "Vendor='" & VendorID & "'"
        DoCmd.Close acForm, currentwin
    End If
End Sub

[Homegrownandy]

on the button wizard: (this may not be the answer/problem but since you asked.

Form Operations > Open form
Select the form name
Open the form with specific information to display
select the two fields that have the relationship.

[Mearntain]

The reason the fields are blank in form 2 is that you set them to blank when you open the form.

Everythings working now, it was a stupid mistake on my part that I was somehow over looking. I had the fields set to go to blank for the initial form when setting up a new vendor, and then copied that form to make this new one, and just completely forgot to remove that from the copy of the form. I feel like an idiot now, but I thought I had everything good with the code. I appreciate the input from everyone