Add or Edit on whether a value exists

[lxh]

Hi there -

I'm a beginner to access and would like to know how I can open a form in 'edit' mode if a certain value exists and 'add' mode id it does not. I posted this to macros but now think it is a query problem?

I have a two tables with a one-to-one relationship. Project and bank. I have a button on the project from which opens the bank form. What I need to do is open the bank form with the correct projectID and corresponding bankID in edit mode if it exists. ANd if not open the bank form in 'add' mode with the correct projectID.

I have been able to use 'setvalue' to set the correct projectID in bank (using) the project form but have had no luck with the rest. Any help would be more than welcome and greatly appreciated.

Project table bank table
ProjectID (PK) (autonumber) bankID (PK) (autonumber)
name projectID
address bankname
bankaddress
Thanks
Lex

 

[dcx693]

You can use VBA code. Specifically, the DoCmd.OpenForm method has a parameter called DataMode where you specify if you want to be able to Add, Edit, make it read-only, or base the permissions on the form's current design property settings. Here's the syntax for the method:

DoCmd.OpenForm formname[, view][, filtername][, wherecondition][, datamode][, windowmode][, openargs]

Look it up in the online help.

 

[lxh]

Okay, but does that mean I have to write a VB program? - I'm a novice and was hoping I could do it in a macro or query using the expression builder?

You can use VBA code. Specifically, the DoCmd.OpenForm method has a parameter called DataMode where you specify if you want to be able to Add, Edit, make it read-only, or base the permissions on the form's current design property settings. Here's the syntax for the method:

DoCmd.OpenForm formname[, view][, filtername][, wherecondition][, datamode][, windowmode][, openargs]

Look it up in the online help.

 

[dcx693]

The OpenForm macro action has the same parameters as the VBA method. However, in order to know what DataMode you want to tell the form to be in, you need to evaluate a condition like "if value exists then edit else add". I'm not a macro user, but I don't think there's a direct way to do that with a macro.

In VBA, I'd use code like this:

Private Sub Command3_Click()
Dim Mode As AcFormOpenDataMode
    
    If True Then
        Mode = acFormAdd
    Else
        Mode = acFormEdit
    End If
        
    DoCmd.OpenForm "Form1", acNormal, , , Mode
End Sub

Just looking at the online help, I wanted to point out that this method might not produce exactly what you want. With acFormAdd a user can Add new records, but can't edit existing records. With acFormEdit, the user can Add and Edit existing records.

 

[lxh]

Does this do the trick?

Is is it not possible to create a query that looks for the necessay projectID in the bank table. Then run a macro to see if this is NULL or not. If it is openform in add mode, if it isn't openform in edit mode and set the values from the query? I'm at work and haven't had a chance to check it yet

Just a thought

 

[Fizzio]

It is possible but a non-efficient way.
Start learning VBA then you can use functions such as DCount to do exactly what you want. Access help is not bad at guiding you along. To get to grips with it, look at the code that access creates itself (eg behind your button to open the form).

Without VBA, you will struggle to create a polished application.