Form with subform - No matching records

[Jaybird]

Okay, with much wrangling I've managed to come up with a query based subform
called frmInvoice4 that is completely updatable. It links four tables
together on these fields:

[Order Entry].Order Number (PK) is linked to HDRPLAT.JOBNO
HDRPLAT.INVNUM (PK) is linked to LINPLAT.INVNUM
LINPLAT.INVNUM is linked to tblInvoice.[Invoice Number] (PK)

I'm not sure if the lack of a primary key on the LINPLAT table is going to
cause me trouble later on or not, but for now it seems to work fine.

This subform (frmInvoice4) is placed on a mainform based on the table Order
Entry with a Combo Box that looks up all the available records for Order
Number. This was designed as a search key that would match up the available
Order Numbers from the Order Entry table to the Invoice records from the
tblInvoice, HDRPLAT, and LINPLAT tables.

Like I said, the recordset of the subform (frmInvoice4) is updatable, but
limited to existing records. When I try to go to a new record, I get an
error stating that a matching record cannot be found. What is the workaround
for this?

From a user's perspective, what I need to be able to do is select an Order
Number from the (Search) Combo Box on the main form, which will then match
the Order Number with existing records in the other tables (where they exist)
and move to a new record where they don't exist.

I tried to modify the Record Source property to include the results of the
subform query, but that causes a parameter error that I did not expect.

Can anybody shine a light through the fog?

 

[Wayne-I-M]

Hi

Use something like this is the combo's NotInList event

Private Sub ComboName_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg As String
Msg = "'" & NewData & "' is not in the database." & vbCr & vbCr
Msg = Msg & "Do you want to add New Record?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("tableName", dbOpenDynaset)

rs.AddNew
rs![SomeField] = NewData
rs.Update
Response = acDataErrAdded
End If
End Sub


Change "ComboName" to what it is
Change [SomeField] the the field name that you would 1st use to start a new
record - ie. to type something in to.
Change tbaleName to the name of you table (that the main form is based on)

________________________________
To search for the record using the combo just use the wizard or something
like this

Private Sub ComboSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[RecordID] = " & Str(Me![ComboSearch])
Me.Bookmark = rs.Bookmark
End Sub


Change ComboSearch to real name of the combo
Change [RecordID] to the real name of the prmary field control


Hope this helps


--
Wayne
Manchester, England.

Okay, with much wrangling I've managed to come up with a query based subform
called frmInvoice4 that is completely updatable. It links four tables
together on these fields:

[Order Entry].Order Number (PK) is linked to HDRPLAT.JOBNO
HDRPLAT.INVNUM (PK) is linked to LINPLAT.INVNUM
LINPLAT.INVNUM is linked to tblInvoice.[Invoice Number] (PK)

I'm not sure if the lack of a primary key on the LINPLAT table is going to
cause me trouble later on or not, but for now it seems to work fine.

This subform (frmInvoice4) is placed on a mainform based on the table Order
Entry with a Combo Box that looks up all the available records for Order
Number. This was designed as a search key that would match up the available
Order Numbers from the Order Entry table to the Invoice records from the
tblInvoice, HDRPLAT, and LINPLAT tables.

Like I said, the recordset of the subform (frmInvoice4) is updatable, but
limited to existing records. When I try to go to a new record, I get an
error stating that a matching record cannot be found. What is the workaround
for this?

From a user's perspective, what I need to be able to do is select an Order
Number from the (Search) Combo Box on the main form, which will then match
the Order Number with existing records in the other tables (where they exist)
and move to a new record where they don't exist.

I tried to modify the Record Source property to include the results of the
subform query, but that causes a parameter error that I did not expect.

Can anybody shine a light through the fog?

 

[Jaybird]

Hey, thanks! I'll try it and let you know...
--
Why are you asking me? I dont know what Im doing!

Jaybird

Hi

Use something like this is the combo's NotInList event

Private Sub ComboName_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg As String
Msg = "'" & NewData & "' is not in the database." & vbCr & vbCr
Msg = Msg & "Do you want to add New Record?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("tableName", dbOpenDynaset)

rs.AddNew
rs![SomeField] = NewData
rs.Update
Response = acDataErrAdded
End If
End Sub


Change "ComboName" to what it is
Change [SomeField] the the field name that you would 1st use to start a new
record - ie. to type something in to.
Change tbaleName to the name of you table (that the main form is based on)

________________________________
To search for the record using the combo just use the wizard or something
like this

Private Sub ComboSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[RecordID] = " & Str(Me![ComboSearch])
Me.Bookmark = rs.Bookmark
End Sub


Change ComboSearch to real name of the combo
Change [RecordID] to the real name of the prmary field control


Hope this helps


--
Wayne
Manchester, England.

Okay, with much wrangling I've managed to come up with a query based subform
called frmInvoice4 that is completely updatable. It links four tables
together on these fields:

[Order Entry].Order Number (PK) is linked to HDRPLAT.JOBNO
HDRPLAT.INVNUM (PK) is linked to LINPLAT.INVNUM
LINPLAT.INVNUM is linked to tblInvoice.[Invoice Number] (PK)

I'm not sure if the lack of a primary key on the LINPLAT table is going to
cause me trouble later on or not, but for now it seems to work fine.

This subform (frmInvoice4) is placed on a mainform based on the table Order
Entry with a Combo Box that looks up all the available records for Order
Number. This was designed as a search key that would match up the available
Order Numbers from the Order Entry table to the Invoice records from the
tblInvoice, HDRPLAT, and LINPLAT tables.

Like I said, the recordset of the subform (frmInvoice4) is updatable, but
limited to existing records. When I try to go to a new record, I get an
error stating that a matching record cannot be found. What is the workaround
for this?

From a user's perspective, what I need to be able to do is select an Order
Number from the (Search) Combo Box on the main form, which will then match
the Order Number with existing records in the other tables (where they exist)
and move to a new record where they don't exist.

I tried to modify the Record Source property to include the results of the
subform query, but that causes a parameter error that I did not expect.

Can anybody shine a light through the fog?

 

[Jaybird]

Well, I guess I'm not as smart as I think I am... I cannot get your code to
work. None of the message boxes prompt me for anything. Is it because the
record source for the combo box includes all of the values from the Order
Entry table but the associated tables (tblInvoice, HDRPLAT, LINPLAT) do not?
(More likely it's because I've noodled around since I descibed the problem to
you, and your solution no longer works.) Or perhaps I haven't explained
myself well enough... The form I am working with is based on a query
(qryInvoiceQuery). This query relates four tables as I have stated earlier.
On this form I have a combo box which searches for matching records.
However, I have customized the row source property to include all records
from the Order Entry table, and matching records from the qryInvoiceQuery.
If I select an Order Number that has no associated records from the
qryInvoiceQuery, no records are returned. What I need is a way to add new
records to the other tables so that they will then match the order number
that I selected.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Hey, thanks! I'll try it and let you know...
--
Why are you asking me? I dont know what Im doing!

Jaybird

Hi

Use something like this is the combo's NotInList event

Private Sub ComboName_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg As String
Msg = "'" & NewData & "' is not in the database." & vbCr & vbCr
Msg = Msg & "Do you want to add New Record?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("tableName", dbOpenDynaset)

rs.AddNew
rs![SomeField] = NewData
rs.Update
Response = acDataErrAdded
End If
End Sub


Change "ComboName" to what it is
Change [SomeField] the the field name that you would 1st use to start a new
record - ie. to type something in to.
Change tbaleName to the name of you table (that the main form is based on)

________________________________
To search for the record using the combo just use the wizard or something
like this

Private Sub ComboSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[RecordID] = " & Str(Me![ComboSearch])
Me.Bookmark = rs.Bookmark
End Sub


Change ComboSearch to real name of the combo
Change [RecordID] to the real name of the prmary field control


Hope this helps


--
Wayne
Manchester, England.

Okay, with much wrangling I've managed to come up with a query based subform
called frmInvoice4 that is completely updatable. It links four tables
together on these fields:

[Order Entry].Order Number (PK) is linked to HDRPLAT.JOBNO
HDRPLAT.INVNUM (PK) is linked to LINPLAT.INVNUM
LINPLAT.INVNUM is linked to tblInvoice.[Invoice Number] (PK)

I'm not sure if the lack of a primary key on the LINPLAT table is going to
cause me trouble later on or not, but for now it seems to work fine.

This subform (frmInvoice4) is placed on a mainform based on the table Order
Entry with a Combo Box that looks up all the available records for Order
Number. This was designed as a search key that would match up the available
Order Numbers from the Order Entry table to the Invoice records from the
tblInvoice, HDRPLAT, and LINPLAT tables.

Like I said, the recordset of the subform (frmInvoice4) is updatable, but
limited to existing records. When I try to go to a new record, I get an
error stating that a matching record cannot be found. What is the workaround
for this?

From a user's perspective, what I need to be able to do is select an Order
Number from the (Search) Combo Box on the main form, which will then match
the Order Number with existing records in the other tables (where they exist)
and move to a new record where they don't exist.

I tried to modify the Record Source property to include the results of the
subform query, but that causes a parameter error that I did not expect.

Can anybody shine a light through the fog?

 

[Jaybird]

Wayne,

I modified the Record Source Property to include all records from all four
tables... Now I can select a record from my combo box (based on Order Entry)
even if it doesn't exist in the three other tables. However, I can't edit
the other tables no matter how I manipulate the relationships. The query in
the Record Source Property is supposed to be updatable... Why can't I update
it? Here's my SQL:

SELECT tblInvoice.[Invoice Number], tblInvoice.[Invoice Date],
tblInvoice.[Shipped Date], tblInvoice.[Batch Number],
tblInvoice.ChrgPartNumber1, tblInvoice.ChrgPartNumber2,
tblInvoice.ChrgPartNumber3, tblInvoice.ChrgPartNumber4, [Order Entry].*,
HDRPLAT.*, LINPLAT.*
FROM (([Order Entry] LEFT JOIN HDRPLAT ON [Order Entry].[Order Number] =
HDRPLAT.JOBNO) LEFT JOIN tblInvoice ON HDRPLAT.INVNUM = tblInvoice.[Invoice
Number]) LEFT JOIN LINPLAT ON tblInvoice.[Invoice Number] = LINPLAT.INVNUM;

Any help you can give me will be appreciated.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Hi

Use something like this is the combo's NotInList event

Private Sub ComboName_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg As String
Msg = "'" & NewData & "' is not in the database." & vbCr & vbCr
Msg = Msg & "Do you want to add New Record?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("tableName", dbOpenDynaset)

rs.AddNew
rs![SomeField] = NewData
rs.Update
Response = acDataErrAdded
End If
End Sub


Change "ComboName" to what it is
Change [SomeField] the the field name that you would 1st use to start a new
record - ie. to type something in to.
Change tbaleName to the name of you table (that the main form is based on)

________________________________
To search for the record using the combo just use the wizard or something
like this

Private Sub ComboSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[RecordID] = " & Str(Me![ComboSearch])
Me.Bookmark = rs.Bookmark
End Sub


Change ComboSearch to real name of the combo
Change [RecordID] to the real name of the prmary field control


Hope this helps


--
Wayne
Manchester, England.

Okay, with much wrangling I've managed to come up with a query based subform
called frmInvoice4 that is completely updatable. It links four tables
together on these fields:

[Order Entry].Order Number (PK) is linked to HDRPLAT.JOBNO
HDRPLAT.INVNUM (PK) is linked to LINPLAT.INVNUM
LINPLAT.INVNUM is linked to tblInvoice.[Invoice Number] (PK)

I'm not sure if the lack of a primary key on the LINPLAT table is going to
cause me trouble later on or not, but for now it seems to work fine.

This subform (frmInvoice4) is placed on a mainform based on the table Order
Entry with a Combo Box that looks up all the available records for Order
Number. This was designed as a search key that would match up the available
Order Numbers from the Order Entry table to the Invoice records from the
tblInvoice, HDRPLAT, and LINPLAT tables.

Like I said, the recordset of the subform (frmInvoice4) is updatable, but
limited to existing records. When I try to go to a new record, I get an
error stating that a matching record cannot be found. What is the workaround
for this?

From a user's perspective, what I need to be able to do is select an Order
Number from the (Search) Combo Box on the main form, which will then match
the Order Number with existing records in the other tables (where they exist)
and move to a new record where they don't exist.

I tried to modify the Record Source property to include the results of the
subform query, but that causes a parameter error that I did not expect.

Can anybody shine a light through the fog?