Opening a form with ID from inputbox

[APhillips]

I am trying to open a form with the ID from an inputbox (the ID is a number), if the ID does not exist a error messege pops up, which works.

If it does exist I want the form to open with the ID which is typed in the inputbox, which does not work, instead, the form does open but is showing blank.

Here is my code:

Dim strfrm, strWhere, strpono, inputBoxRes As String
Dim rs As DAO.Recordset
 
Set rs = CurrentDb.OpenRecordset("TblOrder", dbOpenDynaset, dbSeeChanges)
 
strpono = InputBox("Please enter the purchase order number of the report you wish to view", "Enter PO No")
 
rs.FindFirst "PurchaseOrderNo =" & strpono
 
    If Not rs.NoMatch Then
        strfrm = "frmorder"
        strWhere = "PurchaseOrderNo = " & strpono
        DoCmd.OpenForm strfrm, , , strWhere
    Else
        MsgBox "There is no order with this Purchase Number. Please try again, or look through the report 'all orders' to find out the Purchase Order", vbInformation, "No Purchase Order Found"
    End If
 
Set rsorder = Nothing

Thanks

 

[pr2-eugin]

Hello Philips, Welcome to AWF..

You have declared the variable strpono as String when you stated that it is clearly a number.. so Change the type to Integer or Long.. then try it.. also to be really sure, type cast to Number using CInt or CLng

 

[G37Sam]

Is the purchase order number a string or an integer? If it's a string then you need to add single quotes around it

strWhere = "PurchaseOrderNo = '" & strpono & "'"

 

[APhillips]

Hi, thanks for your suggesion, here is what I changed my code to:

Dim strfrm, strWhere, strpono, inputBoxRes As String
Dim intpono As Integer
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("TblOrder", dbOpenDynaset, dbSeeChanges)

strpono = InputBox("Please enter the purchase order number of the report you wish to view", "Enter PO No")

rs.FindFirst "PurchaseOrderNo =" & strpono

    If Not rs.NoMatch Then
        strfrm = "frmorder"
        intpono = CInt(strpono)
        strWhere = "PurchaseOrderNo = " & intpono
        DoCmd.OpenForm strfrm, acNormal, , strWhere
    Else
        MsgBox "There is no order with this Purchase Number. Please try again, or look through the report 'all orders' to find out the Purchase Order", vbInformation, "No Purchase Order Found"
    End If
    
Set rsorder = Nothing

The inputbox bot needs to be a string, then I've tried to convert the string to an int. Have I done this correctly becuase it still isn't working?


I have also tried using:

strWhere = "PurchaseOrderNo = '" & strpono & "'"

When I try this i get a data type mismatch.

The code below is behind a similar button, which works fine.

Dim strrptname, strWhere As String
Dim intpono As Integer
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("TblOrder", dbOpenDynaset, dbSeeChanges)

strpono = InputBox("Please enter the purchase order number of the report you wish to view")
rs.FindFirst "PurchaseOrderNo = " & strpono
    If Not rs.NoMatch Then
        strrptname = "rptorder"
        strWhere = "PurchaseOrderNo = " & strpono
        DoCmd.OpenReport strrptname, acViewReport, , strWhere
    Else
        MsgBox "There is no order with this Purchase Number. Please try again, or look through the report 'all orders' to find out the Purchase Order", vbInformation, "No Purchase Order Found"
    End If
    
Set rsorder = Nothing

Thanks for the help so far!

 

[APhillips]

Hi guys, really sorry about this, I have solved the problem (the origional vb code worked). I must have changed the record source (in the query) of the form when testing - so this was causing the vba code not to run properly.

Sorry agan for wasting your time.