New To access and stuck

[diasflac]

:banghead:

I have two forms each linked to their own table.

Form 1 has 6 fields 2 of which appear in form 2. Form 2 has these 2 fields and 3 of it's own.

When you go from form 1 into form 2 clicking the button I would like it to look and see if there is already a record for this in Table 2 and display this so you can edit the information.

If there isn't already a record I want it to go to a new record but fill in the 2 fields from form 1 so you only have to fill in the additional details.

What sort of code would I use to achieve this?

 

[pr2-eugin]

What sort of code would I use to achieve this?

NONE.. You need not have the same information in different table.. Look up on Relationship between two tables and MainForm/SubForm design..

 

[missinglinq]

...You need not have the same information in different table...

Usually true, but not always!

Are these two Fields being used as a Composite Primary Key in the Table the First Form is based on and as a Composite Foreign Key in the second Table? A Primary Key is a Field that is unique to a given Record, and so a Composite Foreign Key would mean that only one Record in the given Table would have this same two-field combination.

If this is, in fact, your situation, having the two Fields in multiple Tables is perfectly acceptable.

What are the Datatypes of these two Fields?

Linq ;0)>

 

[diasflac]

Usually true, but not always!

Are these two Fields being used as a Composite Primary Key in the Table the First Form is based on and as a Composite Foreign Key in the second Table? A Primary Key is a Field that is unique to a given Record, and so a Composite Foreign Key would mean that only one Record in the given Table would have this same two-field combination.

If this is, in fact, your situation, having the two Fields in multiple Tables is perfectly acceptable.

What are the Datatypes of these two Fields?

Linq ;0)>

This is correct, the first form is a request for a quotation and the second form is the quotation you get back, each request can only have 1 quotation back from a supplier as quotes for different suppliers are a different request altogether.

First is the reference number which is an autonumber

Second is the RFQ title which is standard text.

 

[pr2-eugin]

Sorry diasflac, I might have spoken too early..

And Sorry I am hijacking the thread too..

Linq, I have never used Composite Primary/Foreign Keys in my applications, I have read about this but never really had the need to use them in my applications, but the statement, intrigues me..

... so a Composite Foreign Key would mean that only one Record in the given Table would have this same two-field combination.

sounds to me like, this would force the One To One relationship in a twisted way.. Is this correct? So, do we have to create this sort of relationship? i.e. can this be defined by normal Relationships or they just sit at the back of the head? Would appreciate some light on this.. Thank you..

 

[missinglinq]

First off, make sure that the ‘reference number’ Field is defined as a Number, in the secondary Table, not an Autonumber Field, as it is in the Primary Table.

In the code below you'll need to replace Go2SecondaryForm with the actual name of your Command Button, SecondaryForm with your second Form's exact name, and RFQ and ReferenceNumber with your exact names. If you have a Space in the reference number name, you'll need to enclose it in Square Brackets:

[reference number]

Best practice, in Access, is to not use Spaces or Special Characters in names.

In the Primary Form

Private Sub Go2SecondaryForm_Click()

 If Nz(Me.RFQ, "") <> "" And Nz(Me.ReferenceNumber, "") <> "" Then
  DoCmd.OpenForm "SecondaryForm", , , , , , Me.RFQ & ";" & Me.ReferenceNumber
 Else
  MsgBox "The RFQ and ReferenceNumber Fields Must Both Have Data Entered!"
 End If

End Sub

In the Secondary Form:

Private Sub Form_Load()

Dim rst As Recordset


If Nz(Me.OpenArgs, "") <> "" Then

 TargetFields = Split(Me.OpenArgs, ";")
 
 Set rst = Me.RecordsetClone
 
 rst.FindFirst "[RFQ]= '" & TargetFields(0) & "' And [ReferenceNumber] = " & TargetFields(1)
  
   If Not rst.NoMatch Then
      Me.Bookmark = rst.Bookmark
   Else
    DoCmd.GoToRecord , , acNewRec
    Me.RFQ = TargetFields(0)
    Me.ReferenceNumber = TargetFields(1)
   End If

rst.Close
Set rst = Nothing

End If

End Sub

Linq ;0)>

 

[diasflac]

Hi,

Apologies for the delay in response and thank you for your help.

It all works except if there is already quote information in the table, it doesn't bring the correct record up?

Here are screen shots of the errors:

 

[pr2-eugin]

What are the Data Types for RFQ and Reference Number?

 

[diasflac]

In table for first form - RFQ is text and RFQ Reference number is autonumber

In table for second form - RFQ is Text and RFQ reference number is text.

RFQ Reference number counts up but has "FY13-" as a precursor so I can't change this to a number, I don't think anyway.

 

[pr2-eugin]

Okay then change it to..

rst.FindFirst "[RFQ]= '" & TargetFields(0) & "' And [ReferenceNumber] = '" & TargetFields(1) & "'"

 

[diasflac]

Okay then change it to..

rst.FindFirst "[RFQ]= '" & TargetFields(0) & "' And [ReferenceNumber] = '" & TargetFields(1) & "'"

Excellent! Thank you both, it's all working now.

 

[pr2-eugin]

Good to hear that.. Glad we could be of help..