Open a form from a form based on two criteria

[clloyd]

I have a database that has a client number with multiple records each with unique numbers. I want to "Bop Around" between the two forms as follows:

The forms filter back and forth based on the client number however if I am on record 19-19-19 when I go back and forth I want to land on the record I am working on in the alternate form. Basically I want to maintain my filter and go to a specific record. Any suggestions.

[PianoMan64]

I have a database that has a client number with multiple records each with unique numbers. I want to "Bop Around" between the two forms as follows:

The forms filter back and forth based on the client number however if I am on record 19-19-19 when I go back and forth I want to land on the record I am working on in the alternate form. Basically I want to maintain my filter and go to a specific record. Any suggestions.

I would need more infromation in order to be able to answer your question.

Normally when you have multiple forms open, there has to be some kind of updating that needs to happen when you're clicking between forms.

I order to be able to answer your questions, you're going to have to provide more details of how the forms are setup and what the relationship betwen all the tables involved are?

So if you would post Table Structure, Queies writtten, Code used in the forms? Anything that would give me some clue as to what you have so far and what you're trying to accomplish.

Hope that helps,

Joe P.

[clloyd]

They are the same table one form holds the detail on the record and the other form is simply used as a means to organize the order the records print in, open dates, close dates, etc. in more of a list format so it is easier to read without scrolling a large form. In this table you can have multiple customers that also have multiple incidents. This is the code I have right now to try and "bop around" from one form to the other. It filters the customer but takes me to the first record. If I am updating the 10th incident, which has a unique number, I want it to take me to the incident number I am working on within that filtered customer in the other form. Does that make sense?

Private Sub RecOpen_Click()
On Error GoTo Err_RecOpen_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frmCustomerRecord"
 
    stLinkCriteria = "[ClientNo]= '" & Me.[ClientNo] & "' "
    DoCmd.GoToRecord "[IncidentNumber] = " & Me.[IncidentNumber] & "' "
 
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_RecOpen_Click:
    Exit Sub
 
Err_RecOpen_Click:
    MsgBox Err.Description
    Resume Exit_RecOpen_Click
 
End Sub

[NeoPa]

Please remember to use the [ CODE ] tags provided as this makes reading and understanding a lot easier.

Admin.

As to your explanation, you may want to reread Joe's request for information again. As a third party, I still find your request very confusing (and it doesn't provide the information requested).

[clloyd]

Please remember to use the [ CODE ] tags provided as this makes reading and understanding a lot easier.

Admin.

As to your explanation, you may want to reread Joe's request for information again. As a third party, I still find your request very confusing (and it doesn't provide the information requested).

I am new at this I don't understand what you mean by [code]

[clloyd]

I must not explain this well previously so I will attempt it again.

I have one table that has multiple entries for one customer. The customer detail is not stored in this table. This table simply stores Incidents or say inventory to make it more generic.

The table in question can have 100 entries for a particular customer however the Incident Number is unique and not duplicated with a customer set however the same Incident Number is not unique within the table and can be used for another Customer number.

For this table I have two forms One is the entire Incident information however it is a large form and with 100 records for one customer we needed a way to organize each incident in more of a list format to see only selective fields in a more organized fashion.

Imagine you had a list of book titles and you had to assign numbers for 100 books in order of 1 to 100 so they appeared in a report in that order. That is what the second form is used for.

A user can be in record 50 in the main form and have to go to the Incident Form to assign a Priority number when he/she goes back to the main form they do not want to scroll through 100 records to find the entry they were working on in the Incident form and visa versa.

This is the code I have so far. Thanks in advance.

Private Sub RecOpen_Click()
On Error GoTo Err_RecOpen_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frmCustomerRecord"
 
    stLinkCriteria = "[ClientNo]= '" & Me.[ClientNo] & "' "
    DoCmd.GoToRecord "[IncidentNumber] = " & Me.[IncidentNumber] & "' "
 
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_RecOpen_Click:
    Exit Sub
 
Err_RecOpen_Click:
    MsgBox Err.Description
    Resume Exit_RecOpen_Click
 
End Sub

[NeoPa]

I'll assume that you are having difficulty using the [ CODE ] tags rather than just ignoring my instructions.

When submitting a post, there is a [ CODE ] tag button visible (It looks like a # and shows CODE when you hover the mouse over it).

The easiest way to use it is to enter your code, select it, then click on the button. The selected text will be enclosed in these tags and the resultant post will be formatted as for code.

[NeoPa]

I am new at this I don't understand what you mean by [code]

Sorry. I missed this earlier, so now I'm sure you weren't ignoring me :) The instructions remain the same though and should help for next time.

[clloyd]

If you read my earlier response I did not know what you meant. I now see to the right on this screen an explanation. I am new at this and just typed not looking to the right of the reply box. I now understand that I need to put

 before and after the code I am having trouble with 

. I will use that in the future. Sorry if I caused any problem.

[NeoPa]

Actually, your earlier post was a perfect response in the circumstances. I'm sorry I missed it.

You're fine :)

[clloyd]

I still have not resolved this issue. Any ideas?

[Annalyzer]

This sounds very much like what I was trying to do. I had way too much data for each record to put on one form, so I created multiple forms and used the same code you were using. It was marginally successful, but then I found out about "tab controls" and discovered that Microsoft is wayyyyyyy better at this than I am. =)

Check into tab controls. They're awesome!

[clloyd]

I use tab controls as well. That will not solve this problem. Please look at the following code which is returning a Syntax error and I would greatly appreciate if anyone can see what I am not please. Thanks

 
 
Private Sub RecOpen_Click()
On Error GoTo Err_RecOpen_Click
 
    Dim stDocName As String
    Dim StLinkCriteria As String
 
    stDocName = "frmRec"
 
    StLinkCriteria = "[Client No]=" & Forms![frmRec]![Client No] & " And [RecNo] = '" & [RecNo]
    DoCmd.OpenForm stDocName, , , StLinkCriteria
 
Exit_RecOpen_Click:
    Exit Sub
 
Err_RecOpen_Click:
    MsgBox Err.Description
    Resume Exit_RecOpen_Click
 
End Sub
 
 

[Stewart Ross]

Hi. In line 9 you have a missing single quote - you have an opening single quote before your reference to field Recno but no closing quote.

If Recno is a number you don't need the quotes at all. If it is a string then you need to add a closing quote like this:

StLinkCriteria = "[Client No]=" & Forms![frmRec]![Client No] & " And [RecNo] = '" & [RecNo] & "'"

It helps us if you would tell us which line is giving you a syntax error - saves us looking in the wrong places for non-existent errors.

You could also get in the habit of compiling your code after making changes, as any errors introduced in doing so will be caught by the compiler.

-Stewart

[clloyd]

Thanks that worked. I am still struggling with it though as I want it retain my filter from the other form. I just takes me to that record within all my records. Thanks