open form, find rec, else new rec?

[r]

I have a form with a dropdown box and button. User should select a company
to view from the dropdown, then click the button to open a form with details
for that company. Some companies won't have details yet, so if a record for
them isn't found, I want to create a new record for the details to be
entered.

How can I do this?!

Thanks.

 

[Guest]

Hi R
So you have a combo box with company names, then you click a button and open
a new form filtered by Company ID.
On Open check count of RecordsetClone, if 0 then you need a message box
"Would you like to add a record", if yes then open form ,if No then cancel.

Here is a code - Open Form event , just change your form name
If Me.RecordsetClone.RecordCount = 0 Then
If MsgBox("No Records, would you like to add a record?", vbOKCancel,
"Confirm") = vbOK Then
DoCmd.OpenForm "frmRepairExpenseLog"
Else: Cancel = True
End If

End If

On Load for the same form
DoCmd.GoToRecord acActiveDataObject, , acNewRec

Thanks
Barb

 

[Steve Schapel]

R,

Make your macro for the Click event of the command button like this...

Condition: DCount("*","CompanyDetails","[CompanyID]=" & [CompanyID])>0
Action: OpenForm
Form Name: "YourDetailForm"
Where Condition: [CompanyID]=[Forms]![YourForm]![YourCombo]

Condition: DCount("*","CompanyDetails","[CompanyID]=" & [CompanyID])=0
Action: OpenForm
Form Name: "YourDetailForm"

Condition: ...
Action: GoToRecord
Record: New

 

[r]

Thanks Steve. I'm confused, though. In the condition, shouldn't
"[companyID]" = [my combobox] ???

If not, I'm not sure where the value of [companyID] is supposed to be coming
from. My understanding is, we're checking the table to see if a record
matched the value selected in the combo box, right?

R,

Make your macro for the Click event of the command button like this...

Condition: DCount("*","CompanyDetails","[CompanyID]=" & [CompanyID])>0
Action: OpenForm
Form Name: "YourDetailForm"
Where Condition: [CompanyID]=[Forms]![YourForm]![YourCombo]

Condition: DCount("*","CompanyDetails","[CompanyID]=" & [CompanyID])=0
Action: OpenForm
Form Name: "YourDetailForm"

Condition: ...
Action: GoToRecord
Record: New

--
Steve Schapel, Microsoft Access MVP

I have a form with a dropdown box and button. User should select a company
to view from the dropdown, then click the button to open a form with details
for that company. Some companies won't have details yet, so if a record for
them isn't found, I want to create a new record for the details to be
entered.

How can I do this?!

Thanks.

 

[r]

Also, the IDs in the tables are "text" -- no way around that as it's what
the linked tables from other systems is giving me. Do I need to adjust my "
" 's to account for that?

R,

Make your macro for the Click event of the command button like this...

Condition: DCount("*","CompanyDetails","[CompanyID]=" & [CompanyID])>0
Action: OpenForm
Form Name: "YourDetailForm"
Where Condition: [CompanyID]=[Forms]![YourForm]![YourCombo]

Condition: DCount("*","CompanyDetails","[CompanyID]=" & [CompanyID])=0
Action: OpenForm
Form Name: "YourDetailForm"

Condition: ...
Action: GoToRecord
Record: New

--
Steve Schapel, Microsoft Access MVP

I have a form with a dropdown box and button. User should select a company
to view from the dropdown, then click the button to open a form with details
for that company. Some companies won't have details yet, so if a record for
them isn't found, I want to create a new record for the details to be
entered.

How can I do this?!

Thanks.

 

[Steve Schapel]

R,

My apologies, yes you are correct.

 

[Steve Schapel]

R,

Yes, there I go making assumptions again.

DCount("*","CompanyDetails","[CompanyID]='" & [CompanyID] & "'")>0

 

[r]

Thanks Steve. I did manage to get those two issues worked out. I made
another post because I can't seem to get my form to open showing the correct
record.

The dropdown doesn't have a control source, it is merely bound to the
customerID field of the table supplying the data.

The customerID in the detail table is text, and I can't figure out how to
make the selection of the bound box match the record in the detail form.

Hopefully this makes sense. I've tried everything I can think of, but I
just get either an error or a filtered detail form with a new record
displayed (though there IS a matching record in the detail table).

??!!

R,

Yes, there I go making assumptions again.

DCount("*","CompanyDetails","[CompanyID]='" & [CompanyID] & "'")>0

--
Steve Schapel, Microsoft Access MVP

Also, the IDs in the tables are "text" -- no way around that as it's what
the linked tables from other systems is giving me. Do I need to adjust my "
" 's to account for that?