Full Version: Trouble populating form if record exists
UtterAccess Forums > Microsoft® Access > Access Forms
Glohamar
I am opening a form based on a query from another form using the following command…
!--c1-->
CODE
DoCmd.OpenForm "PAMMain", , , , acFormAdd

When the user types in a new drawing number, I need to be able to check and be sure that the drawing does not exists, and if it does, I need it to populate the form. There is a subform on this form also.
The strange thing is that the subform populates, but the main form does not.
The thing is, before I was using the PAMMain to add new records using a command button and this command…
CODE
DoCmd.GoToRecord , , acNewRec

I have code that is able to check for the duplicate record and populate the form if the record is there.
Now, since I am opening the form from this other form, I have a feeling that the recordset is not being created to find my record against.
Below is all of the code that worked before I changed the way I am adding a new entry…
CODE
Dim Found
Private Sub DwgNum_BeforeUpdate(Cancel As Integer)
Dim rs As Recordset, C As Control
   Set C = Screen.ActiveControl
   Set rs = Me.RecordsetClone
  
  
   On Error GoTo Err_Find_BeforeUpdate
   ' Try to find a record with a matching value.
   Select Case rs.Fields(C.ControlSource).Type
      ' Find using Numeric data type key value?
      Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
      DB_DOUBLE, DB_BYTE
         rs.FindFirst "[" & C.ControlSource & "]=" & C
      ' Find using Date data type key value?
      Case DB_DATE
         rs.FindFirst "[" & C.ControlSource & "]=#" & C & "#"
      ' Find using Text data type key value?
      Case DB_TEXT
         rs.FindFirst "[" & C.ControlSource & "] = """ & C & """"
      Case Else
         MsgBox "ERROR: Invalid data type for '" & C.Name & "'!"
         DoCmd.CancelEvent
         Exit Sub
   End Select
   ' If a record is found, save the found record's bookmark.
   If rs.NoMatch Then
      Found = Null
   Else
      Found = rs.Bookmark
   End If
   ' If the record is found...
   ' ...cancel the BeforeUpdate event
   ' ...undo changes made to the current record
   ' ...and TAB to the next control to trigger the OnExit routine.
   If Not IsNull(Found) Then
      DoCmd.CancelEvent
      SendKeys "{ESC 2}{TAB}", False
   End If
   Exit Sub
Err_Find_BeforeUpdate:
   MsgBox "ERROR: Err " & Err & ": " & Error$, 48
   DoCmd.CancelEvent
   Exit Sub
End Sub
*************************************************************
Private Sub DwgNum_Exit(Cancel As Integer)
' If the record is found, cancel the OnExit routine to stay
   ' in the control and go find the record.
   If Not IsNull(Found) And Len(Found) <> 0 Then
      DoCmd.CancelEvent
      ' Synchronize the form record with the found record.
      'Screen.ActiveForm.Bookmark = Found  'Original code
      Me.Bookmark = Found
      Found = Null
      'Me.DwgNum.Locked = True
   End If
End Sub


Thanks for any help.
Jack Cowley
If the user enters the drawing number on the first form you can check to see if it exists and if it does open the next form to that record or open it to a new record. Code similar to this should do the trick in a command button on the first for assuming the drawing number has been entered into that form prior to clicking the button:
If Not IsNull(DLookup("[DrawingNumber]", "TableName", "[DrawingNumber] = " & Me.DrawingNumber)) Then
DoCmd.OpenForm "PAMMain", , , "[DrawingNumber] = " & Me.DrawingNumber
Else
DoCmd.OpenForm "PAMMain", , , , acFormAdd
End if
I have assumed that DrawingNumber is a Number and not text.
hth,
Jack
Glohamar
Thanks for the response Jack, but the users are not entering the drawing number on the first form.
That I have is a listbox that show all open drawings, and each drawing can have multiple revisions. When the user gets a drawing to enter into the DB, I was hoping that they could just click "Add New Drawing" and the forms opens to enter the information. But since each drawing has multiple revisions, I did not want to try to have them search through a list to find if that drawing already exists. I thought it would be easier if they just clicked the button and entered the drawing, if it existed it would populate the fields and then they could add the revision in the sub-form.
I hope this information helps in trying to solve my problem.
If this is to difficult to do, I will use your advice and have the user input the drawing before clicking Add New.
Once again, thanks for the help.
Dave
Jack Cowley
Somewhere along the way the user is going to have to enter the drawing number. I would seem to me that it would not make any difference if they enter it before they open the form or after they open the form, but this is your db and you should do it the way you want. At some point you will need to see if the drawing exists and if it does then move to that specific record. If you do not want to use a combobox/listbox of drawing numbers to choose from then use a text box for them to enter the drawing number. Then use code to see if that number exists and if it does then move to that record on the form or move to a new record.
do not see how revisions enter into the picture as I would think that the revision number only depends on the main drawing number...
Maybe I am missing something here....
Jack
Glohamar
What code would I use to find the drawing number that is entered into a textbox? The code that I tried, for some reason, was not working. I thought it might have been because it was not able to create a recordset. Do you have any suggestions?
You are right, the revisions do not matter, I was just trying to give as much info as I could for the help.
Thanks for your help.
Dave
Jack Cowley
Something like this in the After Update event of the text box:
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[DrawingID] = " & Me![MyTextControlName]
If rs.NoMatch Then
MsgBox "Dwg does not exist"
DoCmd.GoToRecord , , acNewRec
Else
Me.Bookmark = rs.Bookmark
End If
hth,
Jack
Glohamar
Thanks for your help Jack.
I used your code and the "Dwg does Not exist" comes up, yet I know the dwg is there. I only have 16 dwg in the table right now, so I know that I am entering it correctly. Plus the sub-form is populated with the correct information for that dwg. The record source for the form is a query if the helps. I have even tried with the table as the record source and it still said that the dwg did not exist.
Is it not creating the recordset because of the way that I am opening the form?
Dave
Jack Cowley
Is the primary key for the drawing you are looking for an Autonumber or is the DrawingNumber the primary key? If the DrawingNumber is the Primary Key then is it Text?
If your Drawing Table is not set up like this then you should consider changing it as you should search for the DrawingID...
tblDrawings
DrawingID (PK and auto)
DrawingNumber
...other necessary fields...
If DrawingNumber is Text then try this line of code:
CODE
rs.FindFirst "[DrawingID] = '" & Me![MyTextControlName]  & "'"

Jack
Glohamar
I have a DwgNumID (autonumber) and then DwgNum as the primary key. And yes the DwgNum is text. I set the DwgNum as primary key to avoid duplicates. Should I have both DwgNumID and DwgNum as PK and and the other as FK?
tried your code an still get dwg does not exist.
Thanks for your help.
Dave
Jack Cowley
I would make the autonumber the primary key. To avoid duplicates use code in the DwgNum controls Before Update event to see if there is a duplicate and go from there. There should be no FK in your drawing table unless the table is the Many side of a One to Many relationship.
hange the PK to the auto field and remove the PK from the DwgNum field. Use code like this in the After Update event of the text box where the user enters the drawing number:
CODE
    Dim rs As Object
If IsNull(DLookup("[DwgNumID]", "NameOfDrawingTable", "[DwgNum] = '" & Me.NameOfControlWithDwgNum & "'")) Then
    MsgBox "Dwg does not exist"
    DoCmd.GoToRecord , , acNewRec
Else
    Set rs = Me.RecordsetClone
    rs.FindFirst "[DwgNumID] = " & DLookup("[DwgNumID]", "NameOfDrawingTable", "[DwgNum] = '" & Me.NameOfControlWithDwgNum  & "'")
    Me.Bookmark = rs.Bookmark
    End If

Change names as appropriate.
hth,
Jack
Glohamar
Sorry for taking so long to get back.
made the changes to the table and added your code.
It is giving me run time error 3021, no current record. I followed the code through and it knows that the dwg is there because it passes the DLookup. But the rs.bookmark states no current record.
Oappreciate your help with this.
Jack Cowley
Do you have the Data Entry property of the form set to Yes or are you opening the form in acFormAdd mode? It sounds like the form is on a new record....
ack
Glohamar
The data entry property is set to yes, and I am using acFormAdd when opening the form. I was trying to avoid someone typing over a record. Is there a better way to open the form blank and still be able to find a drawing if it exists?
Thanks for your patience with me on this.
Dave
Jack Cowley
Dave -
When the Data Entry is set to Yes or you open the form from another for using acFormAdd then you will need to add code before your search code to to set the Data Entry property back to No. You can do this by adding this line of code before the other code you used to find a record:
Me.DataEntry = False
I am glad to help and I hope this will solve your problem...
Jack
Glohamar
Here is where I put the Me.DataEntry = False
!--c1-->
CODE
Private Sub DwgNum_AfterUpdate()
Dim rs As Object
Me.DataEntry = False
If IsNull(DLookup("[DwgNumID]", "PAMMain", "[DwgNum] = '" & Me.DwgNum & "'")) Then
MsgBox "Dwg does not exist"
DoCmd.GoToRecord , , acNewRec
Else
Set rs = Me.RecordsetClone
rs.FindFirst "[DwgNumID] = " & DLookup("[DwgNumID]", "PAMMain", "[DwgNum] = '" & Me.DwgNum & "'")
Me.Bookmark = rs.Bookmark
End If
End Sub

When I run the code I get...
run-time error '3022'
The changes you requested were not successful because they would create duplicate values in the index,
primary key, or relationship. Change the data in the fields that contain duplicate data, remove the index,
For redefine the index to permit duplicate entries and try again.
I do have the DwgNum on the table set for No Duplicates.
I tried putting the code in the before update event and get this error...
Err 2115 The macro or function set to the Before Update or ValidationRule property for this field is preventing
from saving the data in the field.
I have actully tried putting that line in a couple of places and get errors.
Dave
Jack Cowley
Dave -
ove the 'Me.DataEntry = False' after the Else statement. Put a Stop in your code right after the Dim statement then open your form, enter what you know is an exisiting drawing number and see if your DLookup() code is doing what it is supposed to do...
Jack
Glohamar
Jack, I am still having problems. To me it looks like the DLookup is working. I have attached my DB to see if you can see what I am doing wrong. One thing I did notice is that DataEntry is actually set to No on the form.
Thanks for all of your time and help
Dave
Jack Cowley
I am having a hard time getting you db to work as I am getting errors that I should not be getting. I do know that your DLookup() is wrong and it should read:
If IsNull(DLookup("[DwgNum]", "PAMMain", "[DwgNum] = '" & Me.DwgNum & "'")) Then
You have been looking at the DwgNumID and that is the wrong field...
Also, your relationship in the Relationship Window is wrong. The tables should be related on DwgNumID.
Change those two things and see if that helps. I have spent a fair amount of time trying to get rid of an error that makes no sense and I really can't spend any more time on trying to find out what the problem is...
Let me know if this helps...
Jack
Glohamar
Jack,
I am sorry about the db not working. I tried it a few times before I sent it to you to be sure it was error free, but I must have missed something.
Oreally appreciate all of your time spent working on this for me. I am going to have to go about this a different way for the user. Whether they enter the dwg number on the main form and it finds and opens it to that dwg, or if when they click Add New, I will prompt them for the dwgnum and go from there.
Again, thanks for your time, it is greatly appreciated.
Dave
Jack Cowley
Dave -
Here is what I would do.. I would use the DLookup() code in the DwgNum's Before Update event. This will validate your code and you can cancel the update and the cursor will go back to the DwgNum control. The code would be something like:
If Not IsNull(DLookup(...your code...)) Then
MsgBox "This is a duplicate Drawing Number. Please try again."
Cancel = True
Me.DwgNum.Undo
End if
HAs for moving to a selected record you can do that with a combo box created by the Wizard or via other means. Trying to do what you have been trying to do is certainly possible, but I think you are making it more difficult than it needs to be. One other thing.... Consider removing the No Duplicates code form the DwgNum field in the table if you are going to use the Before Update event to validate the input.
That is my 3 cents worth and I know that you now have the code that you need to validate your users input...
Jack
Glohamar
Jack,
Thanks a bunch. I will follow your suggestions as I think your are right. I always seem to make things more difficult than they need to be.
Thanks again.
Dave
Jack Cowley
Dave -
You are most welcome. If you hit a wall you know where to come for answers....or at least suggestions!
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.