Hi LaRana,
First, Using the # symbol in a field can cause problems. The # symbol is an
Access Reserved Character that lets Access know that you are using a date.
It is used like this: #6/23/2007# This is so that Access can recognize that
as a date and not just a number.
It would be a good idea if you can change Loan# to LoanNo or LoanNumber or
LoanID in your tables and your forms.
Back to why your code does not work:
In your code you have Form1 opening in Data entry mode (that is what the ",
, , , acformAdd" does). All you can do is enter new records.
The problem is your code is also trying to find a record and open to it:
"DoCmd.FindRecord (strCbo)". This is not possible when you open the form in
Data Entry Mode using ", , , , acFormAdd".
If you remove the ", , , , acFormAdd" you should be able to go to the record
you desire if it exists. Your OpenToMyRecord combo box must be unbound for
that.
If you remove the "DoCmd.FindRecord (strCbo)" you can add new records to
your database. But your Loan# will not automatically be added.
________________________
If you are trying to add a New Loan Number to your Database automatically I
recommend using the "not in list" event of your combo box.
First, delete the ", , , , acFormAdd" part of your code. This way if the
loan number already exists you can open your form to that loan.
On Form 2 where your OpenToMyRecord combo box is with the new loan number:
1) Make sure that Loan# (and not some other field) is the bound column in
your combo Box query. (if you are not sure how to do this, just let us know
and we can help.)
2) On the Properties sheet of the combo box select the Data tab and change
the "Limit to List" property to Yes.
3) Place this code in the NotInList event of your OpenToMyRecord Combo Box
in Form2 (Change YOURTABLENAME to the name of your table):
Private Sub cboOpenToMyRecord_NotInList(NewData As String, Response As
Integer)
Dim Result
Dim Msg As String
Dim CR As String
CR = Chr$(13)
' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
Select Case MsgBox(" '" & NewData & "' is not in the list." _
& vbCrLf & "" _
& vbCrLf & "Do you want to add '" & NewData & "' to the list?" _
, vbYesNoCancel Or vbQuestion Or vbSystemModal Or _
vbDefaultButton1, " Add Entry To List?")
Case vbYes
' If the user chose Yes, start Form1 in data entry
' mode as a dialog form, passing the new Loan# in
' NewData to the OpenForm method's OpenArgs argument.
DoCmd.OpenForm "Form1", , , , acAdd, acDialog, _
NewData & Me.cboOpenToMyRecord
' Look for the Loan# the user created in Form1.
Result = DLookup("[Loan#]", "YOURTABLENAME", _
"[Loan#] = '" & NewData & "'")
If IsNull(Result) Then
' If the Loan# was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
Else
' If the Loan# was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If
'Return to the list
Case vbNo
' If the user selected no set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
Case vbCancel
' If the user selected no set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
'Delete the entry in the ComboBox
Me!cboOpenToMyRecord = Null
End Select
End Sub
4) ADD THIS CODE TO FORM1:
Private Sub Form_Open(Cancel As Integer)
If Len(Me.OpenArgs) > 0 Then
DoCmd.GoToRecord acForm, "Form1", acNewRec
Me![Loan#] = Me.OpenArgs
Else: DoCmd.GoToRecord acForm, "Form1", acNewRec
End If
End Sub
For more information and different ways of using this event you can do a
google search and find many tutorials and code samples on how to do that.
There are a number of different varieties of the "not in list" event examples
so I suggest you search until you find one that will do what you want and
that you are able to use. Some of them require a lot of coding and others
not so much.
Hope it helps,
Hunter57
http://churchmanagementsoftware.googlepages.com
:
Hi Jana,
I created form1 and form2. Where the records source for form1 is my table
(all data). form2 I am using to ask the user for a loan #. When I open form2
(in form view) I am unable to input a loan number in the "enter loan no"
combo box I created.
I named my Unbound combo box cboOpenToMyRecord_AfterUpdate
Control source is blank
row source is table/query
After update = cboOpenToMyRecord_AfterUpdate()
What am I doing wrong? Here is my code
Option Compare Database
Option Explicit
Private Sub cboOpenToMyRecord_AfterUpdate()
Dim strCbo As Long
strCbo = Me!cboOpenToMyRecord_AfterUpdate
DoCmd.OpenForm "form1", , , , acFormAdd
Forms!form1![loan#].SetFocus
DoCmd.FindRecord (strCbo)
End Sub
:
Hunter57:
I do not know how to write code. I dont guess I know how the combo boxes
work. I have tried changing the properties, however, when I changed it, it
would not let me add a record in add mode. Your suggestions are appreciated.
--
Jana
:
Hi Jana,
You may already know this. If you want to disable editing of an individual
text box or form control, click on the control, open the properties sheet,
click on the Data tab, and changed Locked from No to Yes. They can see the
information but they cannot edit its contents.
I would use unbound combo boxes for them to search with. Here is some code
that I use with a combo box on one form to open other forms to the record I
want.
I am capitalizing parts of the code that you will need to edit.
Private Sub cboOpenToMyRecord_AfterUpdate()
Dim strCbo As Long
strCbo = Me!CBOOPENTOMYRECORD
DoCmd.OpenForm "MYFORMNAME"
Forms!MYFORMNAME![MYPRIMARYKEYFIELDFORMCONTROLNAME].SetFocus
DoCmd.FindRecord (strCbo)
End Sub
MYPRIMARYKEYFIELDFORMCONTROLNAME is the name of the text box or combo box
which shows your table's Primary Key field. It would be something like
CustomerID, txtEmployeeID, cboWorkOrderID, etc.
To allow them to enter new records only you change the code to this:
DoCmd.OpenForm "MYFORMNAME", , , ,acFormAdd
To disable all editing in the form that opens you change the code to this:
DoCmd.OpenForm "MYFORMNAME", , , ,acFormReadOnly
You can let them open queries in read only mode with this code:
DoCmd.OpenQuery "myqueryname", , acReadOnly
I wish you success.
Hunter57
