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.