Form Command button issues.

[miken5678]

The issue i have is upon opening my form the command buttons will not locate previous records however once you input a new record it will allow the function to search and the previous and next record buttons to work(but they still dont show previous records). Once the form is closed and reopened the issue still comes up even with mock data within the tables. Any help on this on the vb side would be appreciated.

thanks

mike

 

[missinglinq]

I assume you're talking about your main form. In Properties - Data you have Data Entry set to Yes. This property only allows new records to be added, you cannot look at existing records in a form when this is set. As strange as it sounds, you do not have to have Data Entry to Yes in order to enter data! So set it to No. If you want your form to always open to a new record, use this code:

Private Sub Form_Load()
 DoCmd.GoToRecord , , acNewRec
End Sub

 

[miken5678]

i guess i did the data entry choice to force it to a new record each time. I honestly had no clue about the limitations it enforces.

In regards to your code and considering im still quite new to vba. Does it matter where i place that code in the list of items? In otherwords i assume there is an optimized way to place code order wise or does it really not matter?

 

[missinglinq]

From Form Design View, use <Ctrl> + <g> to goto the Form's code window. Assuming you don't already have any code in the Form_Load event, directly below the "option" line at the top of the code window (Option Compare Database is the Default) paste in the code I gave you

Private Sub Form_Load()
 DoCmd.GoToRecord , , acNewRec
End Sub

Now find and click on the red Access Key Icon and you'll return to Form Design View. Now run your form; you should be set.

 

[miken5678]

thanks, i assumed it was there i just did not know if there would be an issue with the order in which the code was provided

this is what i did and it works

Private Sub Form_Load()
Me.frmCNRCancellation.Visible = True
DoCmd.GoToRecord , , acNewRec
End Sub

now one more quick question. If you look at the form when it loads i overlayed the subforms. I set it to default at cnr because for the life of me i could not figure out how to set the subform to come up blank like the records do. Is this not attainable when you do overlays?

 

[miken5678]

also, is there a way to hard code the search button to one field. Say my policy number field? That way the user wont have to worry about where the cursor is located for search purposes

 

[missinglinq]

Don't know what code you're using for the search button, but if it's like the standard "Find" function, simply set focus to the appropriate field in your button's code just prior to the search code:

PolicyNumberField.SetFocus
'Search command here

 

[miken5678]

my apologies.

this is what im using

Function FindRecord()
On Error GoTo Err_cmdFindRecord_Click
Screen.PreviousControl.SetFocus
DoCmd.RunCommand acCmdFind
Exit_cmdFindRecord_Click:
Exit Function
Err_cmdFindRecord_Click:
MsgBox Err.Description
Resume Exit_cmdFindRecord_Click
End Function

 

[missinglinq]

Replacing

Screen.PreviousControl.SetFocus

with

PolicyNumberField.SetFocus

should work. Just replace PolicyNumberField with the actual name of your textbox.

 

[miken5678]

in one of my other posts I had another question and just curious if it is possible. I can set a field to a default of nothing or like my line of business field on the main form to a default of cnr. Now, is it possible to set the subform area to come up blank instead of displaying the last form that was ontop or forcing another one to the top layer upon loading?

 

[miken5678]

first try with policynumber.setfocus will not work and says object required.

tried adding screen.policynumber.setfocus and it gave me a compile error mehtod or data member not found

am i just not referencing the text box correctly?

 

[miken5678]

on opening the main form for the first time and clicking search the error

"the expression you entered refers to an object that is closed or doesnt exist"

I assume this is do to the cursor not being limited to one text box or intially clicked in one area?

 

[missinglinq]

policynumber.setfocus should work, assuming your textbox is actually named policynumber. Check your spelling.

 

[miken5678]

I checked the name under the form properties.. im out of guesses


Option Compare Database
Function GotoFirstRecord()
On Error GoTo Err_cmdGotoFirstRecord_Click
DoCmd.GoToRecord , , acFirst
Exit_cmdGotoFirstRecord_Click:
Exit Function
Err_cmdGotoFirstRecord_Click:
MsgBox Err.Description
Resume Exit_cmdGotoFirstRecord_Click
End Function
Function FindRecord()
On Error GoTo Err_cmdFindRecord_Click
txtPolicyNumber.SetFocus
DoCmd.RunCommand acCmdFind
Exit_cmdFindRecord_Click:
Exit Function
Err_cmdFindRecord_Click:
MsgBox Err.Description
Resume Exit_cmdFindRecord_Click
End Function
Function GotoPreviousRecord()
On Error GoTo Err_cmdGotoPreviousRecord_Click
DoCmd.GoToRecord , , acPrevious
Exit_cmdGotoPreviousRecord_Click:
Exit Function
Err_cmdGotoPreviousRecord_Click:
MsgBox Err.Description
Resume Exit_cmdGotoPreviousRecord_Click
End Function
Function GotoNextRecord()
On Error GoTo Err_cmdGotoNextRecord_Click
DoCmd.GoToRecord , , acNext
Exit_cmdGotoNextRecord_Click:
Exit Function
Err_cmdGotoNextRecord_Click:
MsgBox Err.Description
Resume Exit_cmdGotoNextRecord_Click
End Function
Function GotoLastRecord()
On Error GoTo Err_cmdGotoLastRecord_Click
DoCmd.GoToRecord , , acLast
Exit_cmdGotoLastRecord_Click:
Exit Function
Err_cmdGotoLastRecord_Click:
MsgBox Err.Description
Resume Exit_cmdGotoLastRecord_Click
End Function
Function AddRecord()
On Error GoTo Err_cmdAddRecord_Click
DoCmd.GoToRecord , , acNewRec
Exit_cmdAddRecord_Click:
Exit Function
Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click
End Function
Function DeleteRecord()
On Error GoTo Err_cmdDeleteRecord_Click
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
Exit_cmdDeleteRecord_Click:
Exit Function
Err_cmdDeleteRecord_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteRecord_Click
End Function

 

[missinglinq]

You've got your code in the function, which I'm guessing is in a standard module, not in the form's module! Access has no way to know, form here where txtPolicyNumber.SetFocus resides!

I'm all for using functions to carry out common tasks, but to place a single line of code

DoCmd.RunCommand acCmdFind

in a separate function, and then call the function, unnecessarily using resources, instead of simply placing the single line of code in the click event of your button, is just overkill! Think about it; you're replacing one line of code with one line of code!

At any rate, place your line setting the focus in your button code, either

txtPolicyNumber.SetFocus
DoCmd.RunCommand acCmdFind

or

txtPolicyNumber.SetFocus
FindRecord()

And think about what I said about functions. If you have a task that requires 2 or more lines of code to execute, and you use it frequently, from multiple forms/reports/queries, then make it into a function. But if the task requires a single line of code, like invoking Find, or moving to Next Record or Previous Record, etc., simply use the line of code.

 

[miken5678]

so looking at the mdb i uploaded it is overkill to have the form coding pull from the module? I guess everything should be listed within the form? Still new to this and going off of other items ive seen just to give you my point of view. I did not know the find/next commands could be as simple as a single line of code if that is what you are in reference to

 

[missinglinq]

Yes. As I said, when you have tasks that

1. Are used frequently
2. Are called from multiple forms/reports
3. Involve multiple lines of code to perform

it is appropriate to write functions for standard (public) modules and call them when needed. But if you have tasks that only require a single line of code, it's silly to go to the trouble of writing them and storing storing them. After doing all that, you still have to write that single line of code to call them, so why not simply write the actual single line of code necessary to do the job? Writing code is complicated enough, don't make it more so!

Good luck in your project!

Let us know if you hit more snags!

Linq