need serious help in opening forms from listbox and editing records

[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.

[pdebaets]

Try this (first example):

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

or

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

[pdebaets]

in your 2nd exapmple, try:

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

[xwnoob]

in your 2nd exapmple, try:

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

Run time error 2465: Microsoft cannot find the field "|" in your expression. Ill try the first example now ...

[xwnoob]

Try this (first example):

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

or

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

Your first solution only made microsoft access prompt me for the parameter value.But i realised you did not add an S to my tblBorrowerDetail so i added in but i still got the runtime error of 2501: Open form was canceled.

Your second solution also did not work it only gave me runtime error 2501 again

[pdebaets]

2nd example again, with a fix:

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

[pdebaets]

The brackets [] are supposed to enclose an object name. When you have a "." within the brackets, you get the problem.

[xwnoob]

2nd example again, with a fix:

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

Its error time again. This time the debug pointed to the openform code

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

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

Edit: Its the runtime 2501 error

[pdebaets]

A 2501 error occurs when the user cancels the opening of the form. Doesn't make sense. You're not cancelling the form open are you? Is there code in the form ONOpen event procedure? What is it?

Is the table "tblBorrowerDetails" in the recordsource for the form AddNewBorrower?

Is the AddNewBorrower form a data entry form?

[xwnoob]

A 2501 error occurs when the user cancels the opening of the form. Doesn't make sense. You're not cancelling the form open are you? Is there code in the form ONOpen event procedure? What is it?

Is the table "tblBorrowerDetails" in the recordsource for the form AddNewBorrower?

Is the AddNewBorrower form a data entry form?

no code is in the onopen event

Yes tblBorrowerDetails is in the record source

Yes addnewborrower form is a data entry form although i set the data entry property to NO.

I am also puzzled as i have done this correctly before but now when i follow the steps i had taken previously it does not work anymore......

Must there be a filter property in the form properties for it to work??? Also just want to add my form also has a subform so im wondering if theres anything to do with it. previously it had no subform


EDIT: Ok so i set the data entry property to YES in the form property. THe errors message disappeared but now when i click a record only a blank form appears with nothing to edit. I tried it with all codes and they all return blank forms only.... Previously i did not have to set data entry to YES and i still am able to edit the records.Why is access so confusing !

[pdebaets]

It doesn't quite make sense for you to pass a "Where" criteria to a Data Entry form. Try opening the Add form with out the criteria. ex.:

stDocName = "AddNewBorrower"
DoCmd.OpenForm stDocName

[xwnoob]

It doesn't quite make sense for you to pass a "Where" criteria to a Data Entry form. Try opening the Add form with out the criteria. ex.:

stDocName = "AddNewBorrower"
DoCmd.OpenForm stDocName

?? How do i open it without where critiera lol....im not so sure because i copied the code and modified it to my databse,But if theres no where critieria how can i select the record to edit?? Anyway all the codes still only open blank forms...

[pdebaets]

If your form is a Data Entry form, then it is for adding records only and you can't use it ti edit. If your form is named "AddNewBorrower", it seems likely that it is a Data Entry form. Otherwise you might have named it "EditBorrower".

[pdebaets]

You could use one form to do both editing and data entry. Ex.:

stDocName = "frmBorrower"
DoCmd.OpenForm stDocName
with forms!frmBorrower
.DataEntry = True 'Sets the form in data entry mode for adding
end with

or

stDocName = "frmBorrower"
DoCmd.OpenForm stDocName
with forms!frmBorrower
.DataEntry = False 'Sets the form in edit mode
end with

[xwnoob]

If your form is a Data Entry form, then it is for adding records only and you can't use it ti edit. If your form is named "AddNewBorrower", it seems likely that it is a Data Entry form. Otherwise you might have named it "EditBorrower".

hey pde,

I think i managed to get it to work because i followed my previous older databases options . First of all, i only selected 1 field now in the row source....like this SELECT tblBorrowerDetails.BorrowerTitle FROM tblBorrowerDetails ORDER BY [BorrowerTitle] DESC; . I dont know why it has to be only 1 but i just copied it anyway. Then i set the data entry to no. After that, I use this code:

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

However, im not sure if my method will screw up anything in the future so now im quite elated but at the same time worried.
It does not even make sense thought..if possible, could you take a look at my database. If you are willing, ill post it up.