VBA code to look in table and open form

[David Desormeau]

Hi everyone,

I have another question for you all. I have a form A in which I enter a number # and this number is stored in a table 1. In this same form A, I want to add a button. When I click this button I want it to look in a table 2 to see if the number # I entered is already there. If it is not there, I want form B to open and have the number # already in the field associated to it so I only have to fill in the rest. However, if it is already in the table 2(so it is already in the form B), I want it to open the form B and show all the fields that were previously entered including the number #.

Does someone know what VBA code I would need to enter in the Private Sub Button_Click()?

This is what I have now, but it does not work :

Private Sub RP_Click()

If DLookup("NoRecomPaiem", "TblRecomPaiement", "NoRecomPaiem='" & Me.NoRecom & "'") Is Null Then

    DoCmd.OpenForm "FrmRecomPaiement", , , , acFormAdd
    Forms!FrmRecomPaiement!NoRecomPaiem = Me!NoRecom
    Forms!FrmRecomPaiement!NoRecomPaiem = Me!NoRecom
    Forms!FrmRecomPaiement!DateRecom = Me!DateRecom
    Forms!FrmRecomPaiement!NoFacture = Me!NoFacture
    
        DoCmd.Close acForm, "FrmFacture"

Else

    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo Err_RP_Click

    DoCmd.OpenForm "FrmRecomPaiement"

    Set rst = Forms!FrmRecomPaiement.Recordset.Clone

    rst.FindFirst "[NoRecomPaiem] = " & Me.NoRecom
    Forms!FrmRecomPaiement.Bookmark = rst.Bookmark

Exit_RP_Click:
        Exit Sub

Err_RP_Click:
        MsgBox Err.description
        Resume Exit_RP_Click
End If
End Sub

[NeoPa]

It would help for your question to match the code. A question with one set of names associated with code with a different set of names just makes life more complicated.

Generally, when you have an issue like this to deal with, I would consider opening the new form (Form B in your question) and, using the .Recordset of the form, attempt to navigate to the specified record. If that fails then simply set the .DefaultValue property of the relevant control to match the number selected from your old form (Form A in the question). If you prefer, you can also move the recordset to the New Record position at this point too.

[David Desormeau]

I tried to simplify it without all the names and stuff, but if it didn't help, the number # is equivalent to NoRecom (it is called NoRecomPaiem in some forms and tables). The table A is TblFacture and table B is TblRecomPaiemen t. For the forms, form A is FrmFacture and form B is FrmRecomPaiemen t.

[NeoPa]

That makes a certain sense David (so I understand where you're coming from). It simply doesn't work that way though. It makes it harder instead. Easier is to be able to reference across easily and match one to the other. No harm this time, but probably best to remember for future questions :-)

Your last post didn't comment on whether you had managed to get it to work following my instructions. We are interested to hear if it did, or alternatively if you need further assistance.

[David Desormeau]

I'm not sure I fully understand what you said in your first post. If i set it as a default value, how will it know when to open the form to an old record that already has that number or when to open a new record and automatically write the information I already entered (RecomPaiem, NoFacture and DateRecom)?

Also, I don't know if the code I entered in my first post is wrong (I tried it with things I found on google), but it says that there is a type problem with my dlookup line (line 3). NoRecom and NoRecomPaiem are both number types in their respective tables. I even changed the name of NoRecom to NoRecomPaiem (because they are essentially the same thing) but nothing helped (I did change the NoRecom in the code too).

[NeoPa]

David Desormeau:
I'm not sure I fully understand what you said in your first post. If i set it as a default value, how will it know when to open the form to an old record that already has that number or when to open a new record and automatically write the information I already entered (RecomPaiem, NoFacture and DateRecom)?

The DefaultValue property of a control doesn't pertain in any way to which record is currently selected. The only effect it will have is that when on the New Record, the control that it is the DefaultValue of will be set to that value (but importantly without flagging the record as having been changed). Essentially it sets its starting value for new records.

Lines #20 through #25 in your code are an example of moving the recordset of the new form to the required position (as stated in the second paragraph of post #2). It should work perfectly as long as [NoRecomPaiem] is a numeric field. As this code runs after checking for the existence of the record this should never fail.

David Desormeau:
Also, I don't know if the code I entered in my first post is wrong ...

From my perusal of the code I would say :

1. Line #3 treats [NoRecomPaiem] as if it were a string whereas line #24 treats it as numeric. Only one of these can possibly be correct.
2. Lines #6 through #9 set the .Value (The default property) of these four controls, instead of the .DefaultValue as I suggested. This may cause you complications.
3. I see no further problems. You say it doesn't work, but give no further clue as to what went wrong. If you still have an issue with your code you need to repost the updated code and explain what went wrong where (Error message and line # are minimum requirements but don't stop there if more information is required).

[David Desormeau]

I barely changed the code. Like I said, I just changed the name of NoRecom to NoRecomPaiem like the other. This is what it gives :

Private Sub RP_Click()

If DLookup("NoRecomPaiem", "TblRecomPaiementDet", "TblRecomPaiementDet.NoRecomPaiem='" & Me.NoRecomPaiem & "'") Is Null Then

    DoCmd.OpenForm "FrmRecomPaiement", , , , acFormAdd
    Forms!FrmRecomPaiement!NoRecomPaiem = Me!NoRecomPaiem
    Forms!FrmRecomPaiement!DateRecom = Me!DateRecomPaiem
    Forms!FrmRecomPaiement!NoFacture = Me!NoFacture
    
        DoCmd.Close acForm, "FrmFacture"

Else

    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo Err_RP_Click

    DoCmd.OpenForm "FrmRecomPaiement"

    Set rst = Forms!FrmRecomPaiement.Recordset.Clone

    rst.FindFirst "[NoRecomPaiem] = " & Me.NoRecomPaiem
    Forms!FrmRecomPaiement.Bookmark = rst.Bookmark

Exit_RP_Click:
        Exit Sub

Err_RP_Click:
        MsgBox Err.description
        Resume Exit_RP_Click
End If
End Sub

When I go in the form and click on the button called RP, this message shows up :

Which is french for :
Execution Error '3464':
Type of data incompatible in the expression of the criteria

When I click on debug it brings me to the line 3.
My first guess was that the two NoRecomPaiem where not of the same type in their tables, but they are both set to numeric.

[NeoPa]

David:
My first guess was that the two NoRecomPaiem where not of the same type in their tables, but they are both set to numeric.

I'm not sure why you didn't understand my previous post. I expect dealing in a foreign language makes everything that (not very little) bit more complicated. That's an excellent excuse :-D I speak some French, but dealing with technical issues on a French forum site would be at least a massive struggle for me.

I will try again more explicitly (as I now have more direct information too from which I can be sure which is the correct version between lines #3 and #23 in your latest post).

Line #3 treats the value returned by Me.NoRecomPaiem as a string value (due to the quotes (') - See Quotes (') and Double-Quotes (") - Where and When to use them) whereas line #23 treats it correctly (as a numeric value).

A better version of line #3 maight be :

If DLookup("NoRecomPaiem", "TblRecomPaiementDet", "[NoRecomPaiem]=" & Me.NoRecomPaiem) Is Null Then

[David Desormeau]

Ok I think we're getting some where. The previous error is now gone, but now when I click the button another error message pops up :

Error '424':
Object required (The english version should look something like this I think)

The error is still in line 3. I don't have a clue what this error could mean.

[NeoPa]

Can you post what your line #3 now looks like. I'm hoping it's changed but I have no information as to how it looks now.

[David Desormeau]

My code looks like this now :

Private Sub RP_Click()

If DLookup("NoRecomPaiem", "TblRecomPaiementDet", "[NoRecomPaiem]=" & Me.NoRecomPaiem) Is Null Then

    DoCmd.OpenForm "FrmRecomPaiement", , , , acFormAdd
    Forms!FrmRecomPaiement!NoRecomPaiem = Me!NoRecomPaiem
    Forms!FrmRecomPaiement!DateRecom = Me!DateRecomPaiem
    Forms!FrmRecomPaiement!NoFacture = Me!NoFacture
    
        DoCmd.Close acForm, "FrmFacture"

Else

    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo Err_RP_Click

    DoCmd.OpenForm "FrmRecomPaiement"

    Set rst = Forms!FrmRecomPaiement.Recordset.Clone

    rst.FindFirst "[NoRecomPaiem] = " & Me.NoRecomPaiem
    Forms!FrmRecomPaiement.Bookmark = rst.Bookmark

Exit_RP_Click:
        Exit Sub

Err_RP_Click:
        MsgBox Err.description
        Resume Exit_RP_Click
End If
End Sub

It's what I had, but with the minor ajustment you made to line 3.





I just changed line 3 to :

If IsNull(DLookup("NoRecomPaiem", "TblRecomPaiementDet", "[NoRecomPaiem]=" & Me.NoRecomPaiem)) Then

and it worked. However, I now have a problem with this line :

Forms!FrmRecomPaiement!NoFacture = Me!NoFacture

I think the problem might comme from the fact that NoFacture is not in FrmRecomPaiemen t, but in a subform of this form that I think is called Fille8. How would I rewrite this line?

[NeoPa]

Sorry. I missed the Is Null thing when I posted my suggestion. Is Null works in, and only in, SQL code. For VBA the IsNull() function is required as you have found already.

Lines #6 and #7 should use the .DefaultValue property explicitly :

    Forms!FrmRecomPaiement.NoRecomPaiem.DefaultValue = Me.NoRecomPaiem
    Forms!FrmRecomPaiement.DateRecom.DefaultValue = Me.DateRecomPaiem

For line #8 I would need to know the name of the subform control to be able to help you. The name of the subform control need not (but sometimes does) match the name of the form contained within it. More on the details of this can be found in Referring to Items on a Sub-Form.

As an illustration, I will include what the code would be if the subform control were named Fille8 :

    Forms!FrmRecomPaiement.Fille8!NoFacture = Me.NoFacture

There is more than one possible option of course for this code, but that would work under the specified circumstances.

[David Desormeau]

Ok I have Fixed a few problems with your help. However, the DateRecom does not transfer into the FrmRecomPaiemen t form and if I use your defaultvalue line for the NoRecomPaiem it doesn't work either. When I leave it like it was, it works, but the DateRecom still does not work. So here is what I have :

Private Sub RP_Click()

If IsNull(DLookup("NoRecomPaiem", "TblRecomPaiementDet", "[NoRecomPaiem]=" & Me.NoRecomPaiem)) Then

    DoCmd.OpenForm "FrmRecomPaiement", , , , acFormAdd
    Forms!FrmRecomPaiement!NoRecomPaiem = Me!NoRecomPaiem
    Forms!FrmRecomPaiement.DateRecom.DefaultValue = Me.DateRecom
    Forms![FrmRecomPaiement]![Fille8].Form![NoFacture] = Me!NoFacture
    
        DoCmd.Close acForm, "FrmFacture"

Else

    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo Err_RP_Click

    DoCmd.OpenForm "FrmRecomPaiement"

    Set rst = Forms!FrmRecomPaiement.Recordset.Clone

    rst.FindFirst "[NoRecomPaiem] = " & Me.NoRecomPaiem
    Forms!FrmRecomPaiement.Bookmark = rst.Bookmark

Exit_RP_Click:
        Exit Sub

Err_RP_Click:
        MsgBox Err.description
        Resume Exit_RP_Click
End If
End Sub

As I said, the defaultvalue for the DateRecom does not work but the line right over it does. Could it be because it is in a Date/Time format?

[NeoPa]

From your explanation (which seems to cover various problems across multiple lines) I have no idea what problems you have or whether the code posted reflects any or all of these problems.

Perhaps you could explain each problem associated with a line of code that suffers from that problem. If I look at code but have no idea whether it's the bad code or the good code then I have no idea where I am.

[David Desormeau]

Ok, I'll try to explain my problem a little better this time. First of all, I found out that my code had a little bug, because in the case a NoFacture is already entered for a certain NoRecomPaiem it would not a the new NoFacture related to the same NoRecomPaiem (it would only open the record since the NoRecomPaiem already exists). This code is on line 12 to 34(It does not work completely right now, but lets do one problem at a time). Ok So my problem is in the first part of the code (the If part before the else and else if). The code opens a new record, enters the NoFacture, the NoRecomPaiem, but for some reason I do not understand it does not enter the DateRecom. I tried your code (the one displayed on line 7) and I tried a code similar to the one used for the NoFacture and the NoRecomPaiem, but nothing worked. Could this be because the DateRecom is in a Date/Time format?

Private Sub RP_Click()

If IsNull(DLookup("NoRecomPaiem", "TblRecomPaiementDet", "[NoRecomPaiem]=" & Me.NoRecomPaiem)) Then

    DoCmd.OpenForm "FrmRecomPaiement", , , , acFormAdd
    Forms!FrmRecomPaiement!NoRecomPaiem = Me!NoRecomPaiem
    Forms!FrmRecomPaiement.DateRecom.DefaultValue = Me.DateRecom
    Forms![FrmRecomPaiement]![Fille8].Form![NoFacture] = Me!NoFacture
    
        DoCmd.Close acForm, "FrmFacture"
        
ElseIf IsNull(DLookup("NoFacture", "TblRecomPaiementDet", "[NoFacture]= '" & Me.NoFacture & "'")) Then
    
    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo Err_RP_Click

    DoCmd.OpenForm "FrmRecomPaiement"

    Set rst = Forms!FrmRecomPaiement.Recordset.Clone

    rst.FindFirst "[NoRecomPaiem] = " & Me.NoRecomPaiem
    Forms!FrmRecomPaiement.Bookmark = rst.Bookmark
    Forms![FrmRecomPaiement]![Fille8].Form![NoFacture] = Me!NoFacture
    
Exit_RP_Click:
        Exit Sub

Err_RP_Click:
        MsgBox Err.description
        Resume Exit_RP_Click
    DoCmd.Close acForm, "FrmFacture"

Else

    'Dim db As DAO.Database
    'Dim rst As DAO.Recordset

    On Error GoTo Err_RP_Click

    DoCmd.OpenForm "FrmRecomPaiement"

    Set rst = Forms!FrmRecomPaiement.Recordset.Clone

    rst.FindFirst "[NoRecomPaiem] = " & Me.NoRecomPaiem
    Forms!FrmRecomPaiement.Bookmark = rst.Bookmark

'Exit_RP_Click:
        'Exit Sub

'Err_RP_Click:
        'MsgBox Err.description
        'Resume Exit_RP_Click
End If
End Sub

No error pop up appears when clicking on the button since the form opened correctly, but as I said, everything works fine EXCEPT for the DateRecom.