Macro or VBA for opening a form in particular record

[kreativ05]

Hi guys,



I've been bashing my head for a couple of days and I "hit the wall" ...I am a noob and self tought in Access07 but I'm trying to do the following:

I've got a form (Clients_F) that has a subform in datasheet view with a list of quote numbers made by each customer, I have managed to make a Macro after a few hours and googling, that opens another form (Quotes_F) so that it can be edited when a quote number is clicked, but I wish to be able to open Quotes_F as a new record when the empty field under the numbers column in the list is clicked.

Also want to know if it's possible to mantain the Customer's details pre-filled?...I am guessing that this is done through the CustomerID(PK & FK)?

I tried doing it via a Macro or VBA but my knowledge is limited...pls be gentle, still learning!!!

[June7]

Client is synonymous with Customer?

'empty field under the numbers column in the list is clicked' means nothing to me. However, code can check for conditions and open form in the appropriate mode - to existing record or new record. Requires If Then Else or IIf structure.

Where do you want the Customer details to show? Isn't that what the main form Clients_F would show?

I use only VBA, not macros.

If you want to provide db for analysis, follow instructions at bottom of my post.

[kreativ05]

Thanks June7...

Client is synonymous with Customer? ....Yes, sorry brain freeze (Spanish DB to English question )

'empty field under the numbers column in the list is clicked' means nothing to me. However, code can check for conditions and open form in the appropriate mode - to existing record or new record. Requires If Then Else or IIf structure.

Where do you want the Customer details to show? Isn't that what the main form Clients_F would show?


Areas in red stay the same (Customers details) the areas in green would be either edited or would be blank for new entry...so far I have managed that whenever I click the number in the Customer_F it takes me to that quote to edit.

I use only VBA, not macros.

If you want to provide db for analysis, follow instructions at bottom of my post...If you understand a bit of Spanish I will do so

[June7]

I have reviewed many db's in various foreign languages. Spanish shouldn't be as rough as Arabic. Might need some translations but I know what nombre means. Also '# de Invitados' sounds like 'number of invitations'. Advise not to use spaces, special characters/punctuation (underscore is exception) in names. If used, must enclose in [] when used in code or expressions in queries/textboxes. Access won't always supply the [] and then you have to remember to do so.

Executing opening the second form by clicking in the new record row is not something I have done so not sure if would cause complication because that click could initiate a record but that record is not yet committed and no info yet entered and then attempt to open second form and enter data to that same table. Will this result in a blank record in table?

There are various ways to accomplish the data entry you want.

1. your original approach might work

2. enter and commit the new record on first form then open second form to enter child record - the trick is figuring out what event code to open the form

3. form/subform/subsubform structure

4. synchronize two subforms - review http://www.fmsinc.com/MicrosoftAcces...edSubforms.asp

[kreativ05]

June7,

here's a copy of the DB with some boggus data, please forgive me if there is any mistakes in it as I mentioned I am self-tought and have spent many hours going in circles with this project of mine....Thanks for any pointers

[June7]

Have you considered the options listed in previous post? If your design won't work which alternative appeals?

This opens the second form but it shows existing Eventos record, not a new record:

Private Sub PresupuestoBox_Click()
If Me.PresupuestoBox & "" = Isnotnull Then
    DoCmd.OpenForm "Presupuesto", acNormal, , "[IDClientes]=" & Forms!Clientes.IDdelCliente, acFormEdit
Else
    DoCmd.OpenForm "Presupuesto", , , "[IDClientes]=" & Me.IDdelCliente
End If
Me.Requery
End Sub

[kreativ05]

I will check other options in previous posts, is just that I like challenging myself and this was the design I came with...if I have to re-design I will do so, no probs there...is just that I have spent so much time in this one that I want to see how can it be solved without having to redesign, so the way I see it now, as if it was a real life project with a real customer who handed me this DB for me to fix

Once I learn how to come unstuck in problems like this I'll know how to fix it in the future when I see it!!!

Thanks once again

[June7]

I see now that you do have synchronized subforms but the method is slightly different than demonstrated in the tutorial. Are you sure the records are synchronized? If Producto/Servicio should be linked to Evento then I don't see that synchronization happening.

Now that the form is opening but not to new event record, possibly need code to check if the Presupuesto value on first form is null and if is, set the event subform to a new record. Try this behind Presupuesto form:

Private Sub Form_Current()
If IsNull(Forms!Clientes![Presupuesto_Clients subform].Form.PresupuestoBox) Then
Me.datosEventos_presupuesto.Form.DataEntry = True
End If
End Sub

Another method is to use the OpenArgs argument of DoCmd.OpenForm
Private Sub PresupuestoBox_Click()
DoCmd.OpenForm "Presupuesto", , , "[IDClientes]=" & Forms!Clientes.IDdelCliente, , acDialog , Me.Presupuesto & ""
Me.Requery
End Sub

Then behind Presupuesto:
Private Sub Form_Current()
If Me.OpenArgs = "" Then
Me.datosEventos_presupuesto.Form.DataEntry = True
End If
End Sub

Another advisory - use indentation in code and it will be easier to read.

Why code to set textbox ControlSource? Why not just have expression in the ControlSource property? Can't reference a table/query in expression unless that table/query is included in the form's RecordSource. How will the expression know which record to pull value from? Could use DLookup() (domain aggregate function) in the expression to pull in the desired value.

[kreativ05]

Sorry June7, been busy doing other projects and haven't touched Access for some time....thanks for your insight, but I must be having a thick moment here but when you say:

Why code to set textbox ControlSource?

...I can't seem to find the textbox you are refering to

[June7]

Code behind form CalculosPresupuestosTotales:
Me.IVA.ControlSource = ""
and
Me.IVA.ControlSource = "=([Total2] - ([Total2] * [Query1.Descuento]) / 100) * 0.12"

Why not just have the expression in the property, why code to set ControlSource as empty string or expression?