2 tbles, 1 qry, 1 frm, 3 fields, New Rec create, Msg box, Input box, for 3 scenarios

[Rramjet]

Ok June7, you're right - (and I am a complete Access and VBa novice - so am struggling so far and initially tried to create something more complex than wise) so I have tried to simplify it right down...

I have a Form based on a query drawing information from two tables (CLIENT to OM_1: One to many linked by URN).
Three fields: URN (from OM_1: bound, locked);
First Name & Last Name (from CLIENT: bound, locked)
An OM_1 information sheet from client URN_1 needs its data entered.
I open the OM_1 form and search for the URN.
If the URN exists, I just use an “Add record” button which (via OnClick VBa) takes the user to a new record with the URN already entered – ready for data entry. If the URN does not exist – Access defaults to a new record - which is where...

It now gets complicated… I have an “Add client” button (beside the “Add record” button)


When I click the "Add client" button I need a message box (”Please enter the URN of the client you want to add") with an input box - where I can enter the URN of the client that I want to add - and then click OK (or Cancel – which takes the user back to the first record in form OM_1). Clicking OK initiates a procedure that searches for the URN in the CLIENT table (NOT the OM_1 table. A client cannot have an OM_1 record created if they do not exist in the CLIENT table). If a record (that URN) does exist in the CLIENT table, then the VB code takes the user to a new record, adds the URN to the “URN” field (unlocks the field, adds the URN, re-locks the field) and messages ("A new record has been created for “First Name” & “Last Name” with URN “URN. Click OK to begin data-entry or Cancel to delete the new record and return to the OM_1 form"). Clicking OK here takes the focus to the first data entry field (“Date_Completed” located on the second tab of the tab control) and data-entry can proceed ...ELSE IF a record (that URN) is NOT found in the CLIENT table, a message is returned ("A client with URN “URN” does not exist in the CLIENT table. Click OK to try a different URN (returning to the original message box with input box above) or click Cancel to return to the OM_1 form.” Thus going back to the first record in OM_1 – oh and whenever doing this, to make sure the form is sorted by Date_Completed, then First Name and Finally Last Name...
So …I need the code to do that… not much to ask, surely...

[June7]

I was right about what? I presume there is another thread that this one spun off from.

That sounds like a lot of code but it is so confusing I couldn't even begin to offer a suggestion, other than to ask if you have considered a form/subform arrangement?

[Rramjet]

Perhaps you are replying to so many threads ... What I find puzzling about Acces forums - as opposed to other database forums - is that people don't seem to have any idea about how to code to acheive a particular outcome. Usually novices (such a myself) will present a scenario and someone will jump in with a suggestion about how it may be done. Not so with Access. Why is that? Is the code really that complex? I don't understand. Is VBa not a logical code? I presented a logical problem. Are there no standard code lines that acheive each of the steps I outlined in coming to a solution? What is it that I need to do here to elicit some VBa code suggetions toward a solution? Please tell me, I really need to know.

[June7]

You scenario encompasses a lot of ground. I am sure code (macro or VBA) could do each of the behaviors you describe, and probably several ways. I don't know enough about your data structure and form designs to be specific. I also don't want to build it for you because that really is a lot of activity you have described and some of it not easy to code and some I've never tried - such as "takes the user back to the first record in form OM_1". Research each requirement you have, attempt coding that fits and if you encounter specific issue with code, post it for analysis.

Explore the NotInList event of combobox.

However, if a solution is possible without code (or less code), why not use it?

Oh, the form sort order can be established by basing the form on a query that has ORDER BY clause.

[Subwind]

I don't have the experience of some of the others on here, but what you are asking seems like a really really awkward way of doing things.

Why not have 3 forms, a URN list, a URN create form and a OM_1 form. To add a new OM_1 record, you would select the URN from the client list, click add and it will open the OM_1 form with the selected URN. If you do not see the URN you want in the list, create a new URN using the URN create form, then save this, update your list, select the URN in your list and add the new OM_1 record.

If I have read your request correct (which is not that easy to read ), then this would be the most simple way to do what you need. 3 forms, 5 buttons, done.

~Matt

[orange]

I agree with Matt and June. Your post is not clear, you haven't told us anything about WHAT you are wanting to do or trying to support. You have told us HOW you have done "something" to achieve "something". I'm sure it's clear in your mind.
Seems it's like many posts -- getting the WHAT issue in plain English terms before the OP, often (I am a complete Access and VBa novice ) has built something to support some business and asks a question related to custom logic for a button we know nothing about.

Perhaps Matt has divined a solution approach for you.

Good luck with your project.

[Rramjet]

Thank you all for your replies and suggestions. I appreciate them immensely. I have tried the multiple form method - but because I also must deal with OM_2, OM_3, OM_4 ... OM_8 then it rapidly becomes complicated (sure I could search the URN list first, then if a client was on the list, add it to the relevent OM form - but if the client already has a record in the OM form ... and thank you for pointing out that my request is not clear. Perhaps I can clarify...

tblCLIENT
tblOM_1
tblOM_2
(etc)

Three possible scenarios:
1. Client exists, OM instance exists, need to add another OM instance (time 1, time 2 etc)
2. Client exists, OM does not exist, need to add initial OM instance (time 1)
3. Client does not exist ...cannot create an OM instance before a client record is created.

Scenario 1 is easy:
Go to frmOM_1, search for URN, URN found, Click a button that takes the user to a new record with the URN already filled in to add a new record for that URN (time 2 , or 3, etc) - and OM data entry can proceed.

Scenario 2 is also easy:
Go to frmOM_1, search for URN, URN not found, Access defaults to new record, add URN (client exists) so data may be entered.

Scenario 3 is more complicated:
Go to frmOM_1, search for URN, URN not found, Access defaults to new record, add URN (client does not exist), Access has conniptions (cannot add record if client does not exist in CLIENT table) - macros stop, code breaks down, error dialogues appear, extricating oneself becomes problematic ... SO...

I want to short circuit that. Sure, allow scenario 1 to proceed (Client exists, OM URN found) with an "Add record" button"
However, when a URN is NOT found there is an "Add client" button.
Click.
A message box appears (with an input box)
"Type in the URN of the client you want to add and click OK (or Cancel to return to OM_1)"
The user types in the URN and clicks OK
That initiates a search of the tblCLIENT for the URN
Either:
ClientURN is found
OR
Client URN is not found.
IF
client URN is found, return message box with
"Client URN exists, click OK to create a new record (or cancel to return to OM_1)"
ELSE IF
Client URN is NOT found, return message box with
"Client URN does not exist. An OM record cannot be created before the client has a record in the CLIENT table (Click OK to return to OM_1)"

that's it in a nutshell. Hopefully the logic of what I want to achieve is clearer at least... there must be standard bits of code for creating a message box with an input box in it (for example). There must also be standard bits of code to search for a URN from that input box when OK is clicked, etc and so on. I WILL try and find these bits of code and put them together myself - and get back to you all when I am done - it was just that (so far at least) I cannot seem to find any repository of "handy and standard bits of code" on the web, so I thought "why not approach some people who might know..." (shrugs)... anyway - I'll keep looking... thanks again for your suggestions... Rramjet.

[Rramjet]

...oh and by "standard bits of code" I mean (for example) when I say "...takes the user back to the first record on the OM_1 form"), something like:

DoCmd.OpenForm "OM_1"
DoCmd.GoToRecord , , acFirst

...that's what I mean...

[Subwind]

Why do you want a table for each OM_(#) entry? What does OM stand for?

~Matt

[June7]

Why are there multiple OM tables? Are these tables identical in structure? If so, should be 1 table with another field for group attribute. Use filtering to open a single form to the desired group.

Since you seem to understand 'standard bits of code' as evidenced by your last posts, suggest you start coding and debugging. When you have a specific issue, post question and attempted code for analysis.

For scenario 3 explore the NotInList event of combobox.

[orange]

I'm still waiting for a plain English version of the "problem/opportunity".
People still don't understand your OM tables; number of them; structure; what they represent...

[Rramjet]

Sure -

OM = "Outcome Measure". There are 8 different OMs and each contains between twenty and ninety questions. Each needs a separate table and each can have multiple applications (time 1, time 2, time, 3 etc). Thus tblCLIENT to tblOM_n is a one to many relationship (via URN). None of the OM tables are linked - but just to complicate things even further, each OM can be completed in relation to the client by different respondents (eg, self, parent, carer, teacher, etc). BUT I can handle all that no problem (ie I understand how to build relational dtabases - and I do utilise tab controls, subforms and combo boxes and list boxes, macros and lots of different VB code ... BUT I wanted to try and keep it simple for this forum to describe JUST the problem I wanted to resolve.

The problem I presented is a LOGIC problem and should not need ANY explanation of what my database is for.

First step: What is VBa code to create a message box with an input box with two buttons (OK and Cancel).
What is the code that uses the value of the input box (on click OK) to search the tblCLIENT for the URN?
What is the code to return a message box "found" OR "Not found"

etc and so on. I don't understand how it can be that difficult (..or I do ..because I cannot find any standard code examples ...) ..more to the point I don't understand WHY it is such a difficult problem .. I could resolve it in FileMaker in couple of minutes for example) - BUT unfortunately for MS Acces I just don't know VBa code - surely here someone does?

[June7]

What is VBa code to create a message box with an input box with two buttons (OK and Cancel).
What is the code that uses the value of the input box (on click OK) to search the tblCLIENT for the URN?

MsgBox and InputBox are two different things. Which one do you really want? VBA Help has guidance on these intrinsic functions. Validating user entry in InputBox can be tricky. Here is an example of one from my db:

    strRDC = IIf(strMode = "Draft", "X", "?")
    While strRDC = "?"
        strRDC = InputBox("Enter report distribution code:" & vbCrLf & vbCrLf & _
            "     D1    Highway Construction" & vbCrLf & vbCrLf & _
            "     D2    Preconstruction" & vbCrLf & vbCrLf & _
            "     D3    HAP/ACP Mix Designs" & vbCrLf & vbCrLf & _
            "     D4    Northern Region" & vbCrLf & vbCrLf & _
            "     D5    Southeast Region" & vbCrLf & vbCrLf & _
            "     D6    Accepted Products" & vbCrLf & vbCrLf & _
            "     D7    M&O Central Region" & vbCrLf & vbCrLf & _
            "     D8    Special" & vbCrLf & vbCrLf & _
            "     D9    Aviation Construction" & vbCrLf & vbCrLf & _
            "     D10   Concrete Mix" & vbCrLf & vbCrLf & _
            "     D11   AIA Field Maintenance" & vbCrLf & vbCrLf & _
            "     N     Number File" & vbCrLf & vbCrLf & _
            "     X     Copy not for distribution nor filing", "Distribution", "X")
        If UCase(strRDC) Like "D[1-9]" Or UCase(strRDC) Like "D1[0-1]" Or UCase(strRDC) = "N" Or UCase(strRDC) = "X" Then
            'continue with report print
        ElseIf strRDC <> "" Then
            MsgBox "Not an appropriate entry.", vbApplicationModal, "Entry Error"
            strRDC = "?"
        Else
            GoTo Exit_proc
        End If
    Wend

What is the code to return a message box "found" OR "Not found"

Maybe you want something like:
If IsNull(DLookup("ID", "tblClient", "URN='" & InputBox("Enter URN to search") & "'")) Then
MsgBox "URN Not Found", vbOKOnly
Else
MsgBox "URN Found", vbOKOnly
End If

I really don't know what you mean by 'standard code'. There are methods and vocabulary and syntax and these are applied as needed for the specific situation. There is always more than one way to accomplish a requirement.

[Rramjet]

Hey that's great June7 - NOW we're talking! Thank you...

Okay, First thing: I am unsure about the reference to "ID" in that code. Can you explain that for me please?

Second thing: I want that code you just supplied to run after clicking OK in a previous input box (I presume - as you describe - that is what I want (that is if an Input box can have two buttons (OK and Cancel). How can I do that?

Third: .okay...one step at a time...

[Rramjet]

ooops didn't see your (boxed) example (my page didn't load correctly first up ... I'll have a closer look - it does look like something I can ammend ... I'll get back to you... THANK YOU...