Need VBA help to open form/add new record

[bassman197]

Hi,

I have inherited a database that writes contracts and invoices and I now need to add something to it that's beyond my pay grade! Right now a command button on the contract form (Me!) opens vendor contracts (called "multi-confirmations") that match the contract number (EventID). It was created with the wizard using the "open the form and find data to display there" option:

Private Sub Command782_Click()
On Error GoTo Err_Command782_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Multi-Confirmation"

stLinkCriteria = "[EventID]=" & Me![EventID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command782_Click:
Exit Sub
Err_Command782_Click:
MsgBox Err.Description
Resume Exit_Command782_Click

Basically, I want this button to now say: "open the matching forms, but if you don't find any, then create a new record in form "Multi-Confirmation" and then set it's [EventID] to match the [EventID] of the parent form.

Once that first form is opened, I'll add a button to it to allow the user to create additional vendor contracts ("multi-confirmations") with the same number, since ONE contract (for a client) may have MANY vendor contracts.

Any help would be appreciated - I'm trying to get this up and running so contracts can be created within a few days! (what have I gotten myself into!)

 

[RuralGuy]

Do a DCount() on the RecordSource of the Multi-Confirmation form before opening the form. If there are records then proceed as you have, otherwise open the form in the NewRecord mode and fill in the [EventID] control.

 

[bassman197]

I'm trying... just can't get the hang of the DCount Syntax. I am just now cracking open my Access VBA for Dummies book, and this is my first if then else statement, not to mention my first DCount! Here is what I tried:

If DCount("[Event ID]", "tables![Confirmation]", "='Me![EventID]'") = 0 Then
DoCmd.OpenForm "Multi-Confirmation", acNormal, , , acFormAdd
Forms![Multi-Confirmation]![Event ID] = Me![EventID]
Else
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Multi-Confirmation"

stLinkCriteria = "[Event ID]=" & Me![EventID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If
End Sub

In the first line (which is where the problem is), [Event ID] is the field in the table I want the sum from, [Confirmation] is the table (domain) and = Me![EventID] is the criteria (the field from the query that is the record source for the form containing the button with this procedure). The space in [Event ID] and lack of one in [EventID] are not a typo - they were this way when I inherited the database.

As it is, the code cannot find the table. If I leave out "tables!", it seems to find it, but that seems risky. It will then have a problem with the syntax of the criteria.

 

[RuralGuy]

Here's a good link for the Domain function syntax:
http://www.mvps.org/access/general/gen0018.htm

 

[bassman197]

OK, that really helped! Apparently, you don't need to tell DCount you're refering to a table, it's assumed unless you specify queries instead? Also, I didn't need the = sign in criteria?

It now opens the form and finds matching records, if there are any. But, if there are none, it opens a new record, but fails to set the Event ID field to match the one in the parent form (the second line of my THEN commands).

Curiously, if I create an experimental button with only the 2 lines from THEN, it creates a record and sets it to match with no problem. Strange...
Am I missing something obvious?

 

[RuralGuy]

If you will use the code tags (the #) then your formatting is preserved:

If DCount("[Event ID]", "Confirmation", "[EventID] = " & Me.EventID ") = 0 Then
   DoCmd.OpenForm "Multi-Confirmation", acNormal, , , acFormAdd
   [COLOR="Red"]Forms![Multi-Confirmation]![Event ID] = Me![EventID][/COLOR]
Else
   Dim stDocName As String
   Dim stLinkCriteria As String
   stDocName = "Multi-Confirmation"
   stLinkCriteria = "[Event ID]=" & Me![EventID]
   DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

The highlighted line should point to the control in the next form. Does it?

 

[bassman197]

I'm not sure... [Event ID] is the text box in the new form that I'm trying to set to the same value as [EventID] in the parent form (lack of a space in the second one is not a typo - was this way when I inherited it - not good naming...)

 

[RuralGuy]

Change the name of the control in the next form to txtEventID and see what happens. Access could be getting confused.

 

[bassman197]

Changed it in the table, on the target form and in the code, but same result. I'm not sure that's the issue, since in works with my experimental button that ONLY opens a new form and sets the value:

Private Sub Command797_Click()
On Error GoTo Err_Command797_Click
DoCmd.OpenForm "Multi-Confirmation", acNormal, , , acFormAdd
Forms![Multi-Confirmation]![Event ID] = Me![EventID]
Exit_Command797_Click:
Exit Sub
Err_Command797_Click:
MsgBox Err.Description
Resume Exit_Command797_Click

End Sub

It's only in the context of the if-then-else statement that it fails to set the value...

 

[RuralGuy]

Post your If...Then...Else code using the code tags please.

 

[bassman197]

OK, I'm back! Sorry, dumb question: I have seen code tags on another site consisting of

 and

(see the window!), but how does the # work? Encase code between ##? End line with #?

 

[bassman197]

Uh-oh... Upon further experimenting, I see that my DCount didn't actually work after all. I'm running out of time here, so I'm going to give the users two buttons, one to view existing matching records and one to open a new record and set it to the matching number of the contract. Not ideal, but it will be a temporary fix to get us up and running. Thanks for trying! Will keep reading up on DCount...

 

[RuralGuy]

If you use the Post Reply button, you will see a tool menu above the text window. One of the tools is a "#" button that puts code tags in your text window. I would still like to see the If...Then...Else code if you have time.