Need some serious help about opening form from listboxes!!!!

[xwnoob]

Hi,

I am having trouble opening a form and editing a record based on the record i selected from my listbox. I have tried two codes and both failed..ill just state the codes here:

1st code:

DoCmd.OpenForm "AddNewBorrower", , , _
"[tblBorrowerDetails.BorrowerSerialNo]=" & _
"'" & Me.searchborrower.Column(0) & "'"

I encountered run time error '2501' for this. previously when i did this it worked.....but now it cant.

2nd code:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "AddNewBorrower"

stLinkCriteria = "[tblBorrowerDetails.BorrowerSerialNo]='" & Me.[searchborrower.column(0)] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

i encountered run time error'2465' for this one and this is the new code im trying now. Please help as i really tried everything but nothing works.

 

[vbaInet]

What is a runtime error 2501 and a runtime error 2465?

 

[xwnoob]

What is a runtime error 2501 and a runtime error 2465?

Hmm now the errors are both 2465

Error 2465 is microsoft cant find the field "|" in your expression ( i dont even have that in my code o_0)

2nd Error 2465 is microsoft cant find the field "searchborrower.Column(0) in your expression.

 

[vbaInet]

1. What event are you running this code?
2. Is the form you are trying to open just based on tblBorrowerDetails?
3. How many columns does the listbox have?
4. Is the data type of BorrowerSerialNo Text?

 

[xwnoob]

1. What event are you running this code?
2. Is the form you are trying to open just based on tblBorrowerDetails?
3. How many columns does the listbox have?
4. Is the data type of BorrowerSerialNo Text?

1. My event is ShowRecord_Click
2.no theere are subforms which are based on other table. But tblborrowerdetails contain borrowerserialno which links everthing tgt.
3. 3 column
4.BorrowerSerialNo is autonumber

 

[vbaInet]

1. This is a button right?
2. Create a query based on this and use the query as the source of the form. This will get rid of the need to include the table name in the SQL
4. If SerialNo is a Number data type you don't need single quotes.

So after you've done all of the above, your code should be:

stLinkCriteria = "BorrowerSerialNo = " & Me.searchborrower.column(0)

Fyi: If you aren't sure about SQL syntax I would advise that you do it in a query first then look at the SQL view to see how it's written. If you're prefixing the field name by its table name, it should be written like this:

[tblBorrowerDetails[COLOR=Red]][/COLOR].[COLOR=Red][[/COLOR]BorrowerSerialNo]

... and because you don't have spaces or special characters in your field and table name, you can omit the square brackets.