Always open form with same data for a specific customer

[TecnicoAlpha]

Hello..

Situation :

- Created Customer John Smith with CustomerID number 2 on Customer Form (Done)
- On main Menu, select Customer on ComboBox to set values to : (Done)
TempVars!TempCustomerID
TempVars!TempCustomerName
- Open all forms available always with the same Customer (Done)
- Open all forms with the last data entered for this Customer (PROBLEM):banghead:

Can't figure out how to filter and go to that particular record of that Customer.
Tried a query, but couldn't get OpenForm to open it because the form always saved 2 records, one with the data, other empty (Me.Undo didn't work nor BeforeUpdate event code)

I want something like :

- Customer is selected on combobox on main menu, TempVars are set (Done)
- Open form - new record if record doesn't exist
- Else open existing record for this customer

 

[Uncle Gizmo]

How many tables have you got?

 

[TecnicoAlpha]

Hello there, happy to find someone interested.

This is my first database, i'm a newbie at Access, and they gave me a big one, paliative pacient care, almost 80 tables.
Each table has a form linked to it, separated by topics (menus).
Working around this for several months, and it's hard for me to find answers to all sorts of problems.
This last problem is nerve recking, supposed to be simple but...

Please help me..

 

[Uncle Gizmo]

In your first post you mention that you are having difficulty relating a customer to related data. I assume this data is in a separate table?

 

[TecnicoAlpha]

Yes, everything is separate, there's a table for the Patient info, and lots of others with exame results, etc.

Main menu -> Patient info
Main Menu -> Topics -> SubMenu 2 (Inicial Evaluation) -> Forms 2.1, 2.2...
Main Menu -> Topics -> SubMenu 3... -> Forms 3.1, 3.2 ...
Main Menu -> Budgets (Clients)-> Forms...

These Initial Evaluations and the Budgets are only filled once for each patient/cliente,
so everytime you view those forms, it should show the info filled before, not new records.

 

[sgladie@bgsu.edu]

How are your relationships created!? Do you have your primary and foreign keys setup properly!? With 80 tables - that would be the first place I would start. Do you have tables with redundant information? A successful Access database always begins with the tables and relationships - once you have those setup properly the other objects should fall into place much easier!

 

[TecnicoAlpha]

That should be the first place to look at, beacause i left everything as is, i neglected that, and no problems arose until now.
I think i should create a one-to-one relationship, because that way i only have one record per customer, right ?

 

[TecnicoAlpha]

The query filters ok by itself.

I created a one-to-one relationship, the problem remains.

Private Sub btn3_Click()
If IsNull(txtClientName) Then
MsgBox ("Por favor selecione um cliente")
Else
DoCmd.Close acForm, "Menu Orçamentos"

'This doesn´t open, throws na error due to 1-2-1 relationship :
DoCmd.ApplyFilter "SELECT [O_2-AvaliaçãoClínicaInicial].* FROM [O_2-AvaliaçãoClínicaInicial] WHERE BID = TempVars!TempBID"

' Tried each one of these, they open the form on a new record, and throw na error if i try to save :
DoCmd.OpenForm "O_2 - Avaliação Clínica Inicial", , , "[Event] = 'qryO_2-AvaliaçãoClínicaInicial'"
DoCmd.OpenForm "O_2 - Avaliação Clínica Inicial", acNormal, "qryO_2-AvaliaçãoClínicaInicial"

End If
End Sub

- How to open the form on that record ceated before for this particular customer ?

I want something like :

- Customer is selected on combobox on main menu, TempVars are set
- Open form - new record if record doesn't exist
- Else open existing record for this customer

 

[sgladie@bgsu.edu]

TecnicoAlpha
I am not sure, but it sounds like you "inherited" this database and the forms and other objects were already setup, am I correct in that assumption? It also sounds like you are trying to "rush" and create something on the fly.

I cannot stress enough the importance of having the tables and relationships properly setup. If I were you I would look at all the tables, get to know them, and see if they are all setup properly. (make a copy of your existing database so your users could still utilize while you work). I know it will take you longer time, maybe time you don't have right now, but in the long run, it will prove beneficial.

Fixes on the fly especially with improper relationships will just drive you crazy!! There are great resources online, even videos to watch if you need to learn relationships more indepth!

I hope this helps!!