Open selected customer in my main database

[ipbr21054]

This code is 99% down apart from one issue.

The code searches for a blank cell in column P
A msgbox is shown with the first found cell & the user is asked if its the customers they are looking for.
The user in this case selects YES to this question & the customer is then selected on the worksheet of which will bew column A

The user is shown another msgbox asking do you wish to open in main database, now this is where im stuck.
Selecting YES should open the main database & the customers records shown.
The line in Red below is the point i cant figure out on how to continue.


Code is supplied below.

Private Sub BlankInvoiceCell_Click()
    Dim myRange As Range
    Dim answer As Integer
    Set myRange = Range("Table23").ListObject.DataBodyRange
    For Each myCell In Intersect(Columns("P"), myRange) ' COLUMN WITH NO INVOICE NUMBER
    If IsEmpty(myCell) Then
      With Range("A" & myCell.Row)
        If MsgBox("EMPTY INVOICE CELL LOCATED AT ROW: " & myCell.Address(0, 0) & vbCr & vbCr & _
          "THE CUSTOMER IS : " & .Value & vbCr & vbCr & _
          "IS THIS THE CUSTOMER YOU ARE LOOKING FOR ?", vbCritical + vbYesNo, "CUSTOMER INVOICE SEARCH") = vbYes Then
          .Select
          answer = MsgBox("OPEN CUSTOMERS FILE IN MAIN DATABASE ?", vbYesNo + vbInformation, "OPEN DATABASE MESSAGE")
          If answer = vbYes Then
          Database.LoadData Sheets("DATABASE"), .Select
        Else
          Unload DatabaseSearchForm
        End If
          Exit Sub
          End If

      End With
    End If
  Next myCell
  MsgBox "THERE ARE NO BLANK INVOICE CELLS" & vbNewLine & vbNewLine & "SO THE SEARCH IS NOW COMPLETE", vbInformation, "BLANK CELL MESSAGEE"
  Range("A5").Select
End Sub

 

[ChrisGT7]

You have found the row where there is no invoice number (myCell.Row).

So now, you have to retrieve from this row the customer ID (if there is no ID, then use the customer name) from the appropriate column (ex. Cells(myCell.Row, "B").Value) and use it to find (Range.Find()) the row from the customer database.

 

[ipbr21054]

Im confused by that sorry,can you just advise please what i need to write after If answer = vbYes Then

The selected cell is the customers name