Checking if record already exist

[Amour]

I am really new to VBA, currently I have one form were I create meetings,
than another one were the individual is inputted (all works great - this is
not the problem). I am trying to open a form(Travel) from that form by
clicking on a button (This I already have and that part works). I want the
newly opened form (Travel) to automatically populate a few fields (that you
can not see on the form) that are the primary fields. example: [field1] =
Me.[field1] and [field2] = Me.[field2]. Now if the record already exist then
I want it to populate the form.

So far I have this:
Private Sub btnTravel_Click()
On Error GoTo Err_btnTravel_Click

Dim DocName As String
DocName = "frmTravel"
DoCmd.OpenForm DocName, , , "[Field1]='" & Me.[Field1] & "' AND
[Field2]='" & Me.[Field2] & "'"

Exit_btnTravel_Click:
Exit Sub

Err_btnTravel_Click:
MsgBox Error$
Resume Exit_btnTravel_Click
End Sub

The way it is working right now is:
1.) If it exist in the table then it comes up
2.) but if it does not exist then the form is blank and does not show any of
the options to enter data.

What I want is for it to come up both ways:

1.) If it exist in the table then it comes up
2.) but if it does not exist then the form is to be populated by the other
forms putting both fields on the new form so I can add a record.

I might even take a different approach.
By passing Me.[Field1] and Me.[Field2] using the OpenArgs property. Then
in the form open event or the form load event, I could use the values passed
to check for a matching record. If found, move to that record or if not
found, get the
values from the other forms (that would need to be open) and create a new
record.

I really don't understand how to do this and was wondering if someone could
be so kind as to show me how this might be done...
Please help.

Thank you for any help.

 

[Ofer Cohen]

You can pass the fields values using the OpenArgs, put a seperator between
them, like ($) Something that wont come up in the string

DoCmd.OpenForm DocName, , , "[Field1]='" & Me.[Field1] & "' AND
[Field2]='" & Me.[Field2] & "'" , , ,Me.[Field1] & "$" & Me.[Field2]


On the OnLoad event of the frmTravel form, write the code to split the open
args and assign it to the text boxes

If Me.NewRecord Then
Me.[Field1] = Split(Me.OpenArgs,"$")(0)
Me.[Field2] = Split(Me.OpenArgs,"$")(1)
End If

 

[Amour]

Thank you for your response, but I am still having the problem. The form
comes up blank and by this I mean the form does not even have any options
with in it.




This is what I have:

Private Sub btnTravel_Click()
On Error GoTo Err_btnTravel_Click
Dim DocName As String
DocName = "frmTravel"
DoCmd.OpenForm DocName, , , "[Field1]='" & Me.[Field1] & "' AND
[Feild2]='" & Me.[Field2] & "'", , , Me.[Field1] & "$" & Me.[Field2]
Exit_btnTravel_Click:
Exit Sub

Err_btnTravel_Click:
MsgBox Error$
Resume Exit_btnTravel_Click
End Sub

And on the form Travel I have in the load:

Private Sub Form_Load()
If Me.NewRecord Then
Me.[Field1] = split(Me.OpenArgs, "$")(0)
Me.[Field2] = split(Me.OpenArgs, "$")(1)
End If
End Sub



Thank You for any help.....

You can pass the fields values using the OpenArgs, put a seperator between
them, like ($) Something that wont come up in the string

DoCmd.OpenForm DocName, , , "[Field1]='" & Me.[Field1] & "' AND
[Field2]='" & Me.[Field2] & "'" , , ,Me.[Field1] & "$" & Me.[Field2]


On the OnLoad event of the frmTravel form, write the code to split the open
args and assign it to the text boxes

If Me.NewRecord Then
Me.[Field1] = Split(Me.OpenArgs,"$")(0)
Me.[Field2] = Split(Me.OpenArgs,"$")(1)
End If

--
Good Luck
BS"D

I am really new to VBA, currently I have one form were I create meetings,
than another one were the individual is inputted (all works great - this is
not the problem). I am trying to open a form(Travel) from that form by
clicking on a button (This I already have and that part works). I want the
newly opened form (Travel) to automatically populate a few fields (that you
can not see on the form) that are the primary fields. example: [field1] =
Me.[field1] and [field2] = Me.[field2]. Now if the record already exist then
I want it to populate the form.

So far I have this:
Private Sub btnTravel_Click()
On Error GoTo Err_btnTravel_Click

Dim DocName As String
DocName = "frmTravel"
DoCmd.OpenForm DocName, , , "[Field1]='" & Me.[Field1] & "' AND
[Field2]='" & Me.[Field2] & "'"

Exit_btnTravel_Click:
Exit Sub

Err_btnTravel_Click:
MsgBox Error$
Resume Exit_btnTravel_Click
End Sub

The way it is working right now is:
1.) If it exist in the table then it comes up
2.) but if it does not exist then the form is blank and does not show any of
the options to enter data.

What I want is for it to come up both ways:

1.) If it exist in the table then it comes up
2.) but if it does not exist then the form is to be populated by the other
forms putting both fields on the new form so I can add a record.

I might even take a different approach.
By passing Me.[Field1] and Me.[Field2] using the OpenArgs property. Then
in the form open event or the form load event, I could use the values passed
to check for a matching record. If found, move to that record or if not
found, get the
values from the other forms (that would need to be open) and create a new
record.

I really don't understand how to do this and was wondering if someone could
be so kind as to show me how this might be done...
Please help.

Thank you for any help.

 

[Ofer Cohen]

Hi,

Check the record source of the form, run it separatly, can you add records
there?
Mybe the problem is with the record source and not with you code.

Also, to check the code locate the cursor on this line
If Me.NewRecord Then
Press F9, you'll get a red line, that mean that the code will stop there
when you run the form.
Step the code using the F8 key, see if the code move into the next line.
You can check the values in the split(Me.OpenArgs, "$")(0) using the
Immidiate window by writing a question mark and then the variable you want to
check.

?split(Me.OpenArgs, "$")(0)

And then press enter, what value is returned?

To continue the code you can press F5
To remove the code break, locate the cursor again in that line and press F9
again

--
Good Luck
BS"D

Thank you for your response, but I am still having the problem. The form
comes up blank and by this I mean the form does not even have any options
with in it.




This is what I have:

Private Sub btnTravel_Click()
On Error GoTo Err_btnTravel_Click
Dim DocName As String
DocName = "frmTravel"
DoCmd.OpenForm DocName, , , "[Field1]='" & Me.[Field1] & "' AND
[Feild2]='" & Me.[Field2] & "'", , , Me.[Field1] & "$" & Me.[Field2]
Exit_btnTravel_Click:
Exit Sub

Err_btnTravel_Click:
MsgBox Error$
Resume Exit_btnTravel_Click
End Sub

And on the form Travel I have in the load:

Private Sub Form_Load()
If Me.NewRecord Then
Me.[Field1] = split(Me.OpenArgs, "$")(0)
Me.[Field2] = split(Me.OpenArgs, "$")(1)
End If
End Sub



Thank You for any help.....

You can pass the fields values using the OpenArgs, put a seperator between
them, like ($) Something that wont come up in the string

DoCmd.OpenForm DocName, , , "[Field1]='" & Me.[Field1] & "' AND
[Field2]='" & Me.[Field2] & "'" , , ,Me.[Field1] & "$" & Me.[Field2]


On the OnLoad event of the frmTravel form, write the code to split the open
args and assign it to the text boxes

If Me.NewRecord Then
Me.[Field1] = Split(Me.OpenArgs,"$")(0)
Me.[Field2] = Split(Me.OpenArgs,"$")(1)
End If

--
Good Luck
BS"D

I am really new to VBA, currently I have one form were I create meetings,
than another one were the individual is inputted (all works great - this is
not the problem). I am trying to open a form(Travel) from that form by
clicking on a button (This I already have and that part works). I want the
newly opened form (Travel) to automatically populate a few fields (that you
can not see on the form) that are the primary fields. example: [field1] =
Me.[field1] and [field2] = Me.[field2]. Now if the record already exist then
I want it to populate the form.

So far I have this:
Private Sub btnTravel_Click()
On Error GoTo Err_btnTravel_Click

Dim DocName As String
DocName = "frmTravel"
DoCmd.OpenForm DocName, , , "[Field1]='" & Me.[Field1] & "' AND
[Field2]='" & Me.[Field2] & "'"

Exit_btnTravel_Click:
Exit Sub

Err_btnTravel_Click:
MsgBox Error$
Resume Exit_btnTravel_Click
End Sub

The way it is working right now is:
1.) If it exist in the table then it comes up
2.) but if it does not exist then the form is blank and does not show any of
the options to enter data.

What I want is for it to come up both ways:

1.) If it exist in the table then it comes up
2.) but if it does not exist then the form is to be populated by the other
forms putting both fields on the new form so I can add a record.

I might even take a different approach.
By passing Me.[Field1] and Me.[Field2] using the OpenArgs property. Then
in the form open event or the form load event, I could use the values passed
to check for a matching record. If found, move to that record or if not
found, get the
values from the other forms (that would need to be open) and create a new
record.

I really don't understand how to do this and was wondering if someone could
be so kind as to show me how this might be done...
Please help.

Thank you for any help.

 

[Amour]

Thank you I found the problem (very simple) did not have addition option to
Yes on the form. Now I have to look into dup's:

Again Thank You

Hi,

Check the record source of the form, run it separatly, can you add records
there?
Mybe the problem is with the record source and not with you code.

Also, to check the code locate the cursor on this line
If Me.NewRecord Then
Press F9, you'll get a red line, that mean that the code will stop there
when you run the form.
Step the code using the F8 key, see if the code move into the next line.
You can check the values in the split(Me.OpenArgs, "$")(0) using the
Immidiate window by writing a question mark and then the variable you want to
check.

?split(Me.OpenArgs, "$")(0)

And then press enter, what value is returned?

To continue the code you can press F5
To remove the code break, locate the cursor again in that line and press F9
again

--
Good Luck
BS"D

Thank you for your response, but I am still having the problem. The form
comes up blank and by this I mean the form does not even have any options
with in it.




This is what I have:

Private Sub btnTravel_Click()
On Error GoTo Err_btnTravel_Click
Dim DocName As String
DocName = "frmTravel"
DoCmd.OpenForm DocName, , , "[Field1]='" & Me.[Field1] & "' AND
[Feild2]='" & Me.[Field2] & "'", , , Me.[Field1] & "$" & Me.[Field2]
Exit_btnTravel_Click:
Exit Sub

Err_btnTravel_Click:
MsgBox Error$
Resume Exit_btnTravel_Click
End Sub

And on the form Travel I have in the load:

Private Sub Form_Load()
If Me.NewRecord Then
Me.[Field1] = split(Me.OpenArgs, "$")(0)
Me.[Field2] = split(Me.OpenArgs, "$")(1)
End If
End Sub



Thank You for any help.....

You can pass the fields values using the OpenArgs, put a seperator between
them, like ($) Something that wont come up in the string

DoCmd.OpenForm DocName, , , "[Field1]='" & Me.[Field1] & "' AND
[Field2]='" & Me.[Field2] & "'" , , ,Me.[Field1] & "$" & Me.[Field2]


On the OnLoad event of the frmTravel form, write the code to split the open
args and assign it to the text boxes

If Me.NewRecord Then
Me.[Field1] = Split(Me.OpenArgs,"$")(0)
Me.[Field2] = Split(Me.OpenArgs,"$")(1)
End If

--
Good Luck
BS"D


:

I am really new to VBA, currently I have one form were I create meetings,
than another one were the individual is inputted (all works great - this is
not the problem). I am trying to open a form(Travel) from that form by
clicking on a button (This I already have and that part works). I want the
newly opened form (Travel) to automatically populate a few fields (that you
can not see on the form) that are the primary fields. example: [field1] =
Me.[field1] and [field2] = Me.[field2]. Now if the record already exist then
I want it to populate the form.

So far I have this:
Private Sub btnTravel_Click()
On Error GoTo Err_btnTravel_Click

Dim DocName As String
DocName = "frmTravel"
DoCmd.OpenForm DocName, , , "[Field1]='" & Me.[Field1] & "' AND
[Field2]='" & Me.[Field2] & "'"

Exit_btnTravel_Click:
Exit Sub

Err_btnTravel_Click:
MsgBox Error$
Resume Exit_btnTravel_Click
End Sub

The way it is working right now is:
1.) If it exist in the table then it comes up
2.) but if it does not exist then the form is blank and does not show any of
the options to enter data.

What I want is for it to come up both ways:

1.) If it exist in the table then it comes up
2.) but if it does not exist then the form is to be populated by the other
forms putting both fields on the new form so I can add a record.

I might even take a different approach.
By passing Me.[Field1] and Me.[Field2] using the OpenArgs property. Then
in the form open event or the form load event, I could use the values passed
to check for a matching record. If found, move to that record or if not
found, get the
values from the other forms (that would need to be open) and create a new
record.

I really don't understand how to do this and was wondering if someone could
be so kind as to show me how this might be done...
Please help.

Thank you for any help.