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 TblRecomPaiement. For the forms, form A is FrmFacture and form B is FrmRecomPaiement.

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

[NeoPa]

David, while we are looking at this together, why don't you follow my suggestions? If you feel you would like to revert to a differnt style and try doing other things after we've finished, that you can do too. While I'm making suggestions though, please follow them and report on how they worked, rather than how your (amended) code worked. That is useless to me as far as information goes. How can it make sense to give a response based on code I haven't suggested. Your explanations are also pretty short of any real information too, so I'm left with almost nothing to work with.

I've just thrown away the rest of the post I had prepared as I just noticed that your original line #11 closes the form before allowing the operator to interact with it at all. This is quite contrary to what you said in your question and, if I can correctly work out what you're trying to do, in spite of this being not what you say you are doing, I would say you are actually trying to add a record to the table if the record doesn't already exist.

If this is the case, then the approach you are using can work but is unnecessarily complicated and clumsy (This can cause more problems than simply those while attempting to develop such a solution).

Please confirm my thinking on this (or alternatively give some other explanation for the code, so I know what you're trying to do). Recordset processing provides one solution to this and SQL provides another. I tend to prefer the latter myself, but will happily suggest code of either type if you specify a preference. Clearly, I need to know the table name before I can look at either approach though.

[David Desormeau]

I actually tried the code you gave me in post #12, but it did not work and that is why I tried what I have right now. As you can see, line 7 is the code you gave me in post 12 (with the exception that DateRecomPaiem is DateRecom, that was a mistake on my part when I told you the name of my fields. The actual name is DateRecom). What I'm trying to do is on the click of a button in one form (FrmFacture) either open a new record in a form that contains a subform and transfer some information from the form on which the button is placed (this is the IF part of the code), then if the NoRecomPaiem already exists in the second form I don't want to make a new record I just want it to open the record with that NoRecomPaiem and add a new record in the subform that takes the NoFacture from the form with the button (the else if part, which does not work right now because it opens the right record but replaces the first NoFacture instead of adding a new one in the subform) and finally if the NoRecomPaiem is already there and the NoFacture also, I just want it to open the form on the correct record (this is the else part, this part works fine).

I'm truly sorry if I'm causing you a lot of trouble with my posts, I am by no means a VBA genuis and I usually try different things by looking around on google. If an easier way of doing this exists, I will be more than happy to try it out, but for some reason I don't understand, the code you posted on post 12 does not work (the NoRecom Paiem does not transfer and in the date field all I see is ########, but no error pop ups appear and the form opens on a new record. however, the code for the NoFacture you posted works fine, I found one that worked on google just before you posted yours that's why I did not try it until now)

[David Desormeau]

Sorry for the double post, but I got the elseif part working. This is what I did (I only include the part I changed) :

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].SetFocus
    DoCmd.GoToControl ("NoFacture")
    DoCmd.GoToRecord , "", acNewRec
    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"
 

[NeoPa]

I sent you a Private Message about this question David. It is important you read that before continuing, as I cartainly can't help you any further until I get some sensible answers from you. This is explained in the PM (which I'm guessing you haven't discovered yet). To access PMs simply sign in to the site and go to any page. At the tope is something saying Inbox(n), where you will find links to review all your Private Messages.