Update conditions not working

[FP1]

I'm trying to sync a form/subform where they are based on a common
key (Appid). The situation is I want users to be able to add records
to the Form table, but only if a corresponding record (Appid) exists
in the child window. (I do a query to only those records which do not
exist in the table to be editted.) This the 'current' attempt.

Either the child window says EOF=FALSE when it's NOT, I get absolute
position of -1 when it's NOT, or Bookmark not accessible when I tried
to use that. There MUST be a way to do this. ALL afternoon on this.
Good ole convoluted Access. What am I doing wrong here? This is the
Before_update code. (The child table, I should point out, is not
updateable on the form) Thanks!


Dim appid
Dim strCriteria
Dim rsMe As DAO.Recordset
Dim rsChild As DAO.Recordset
Dim cForm As Form

' if we're adding a new record, make sure a record with that key
doesn't already exist
' in the local table. If it does, no can do

Set cForm = Me.chdPatCountryApp.Form 'child form with master
records
Set rsMe = Me.Recordset
Set rsChild = cForm.Recordset

Dim updateAllowed As Boolean
updateAllowed = True

If cForm.Recordset.AbsolutePosition < 0 Then
updateAllowed = False
End If

If Not updateAllowed Then
Cancel = True
Me.Undo
Exit Sub
End If

If IsNull(txtAppId.Value) Then
appid = cForm("appid")
Me("tblFP_PatCountryApplication.appid") = appid
End If

Me("caseNumber") = cForm("caseNumber")

If Me.NewRecord Then
Me("appid") = cForm("appid")
RecordAdded = True
Else
RecordAdded = False
End If
End Sub

 

[John W. Vinson]

I'm trying to sync a form/subform where they are based on a common
key (Appid). The situation is I want users to be able to add records
to the Form table, but only if a corresponding record (Appid) exists
in the child window. (I do a query to only those records which do not
exist in the table to be editted.) This the 'current' attempt.

If the Form's table is related to the Subform's table by Appid in a one to
many relationship, you have a "chicken or egg" problem here! In order for a
record to exist in the chld table, one must already exist in the parent table;
you're saying that in order to add a record to the parent table you must
already have a record in the child table. You can't have it both ways!

Am I misinterpreting the problem?

John W. Vinson [MVP]

 

[OV]

If the Form's table is related to the Subform's table by Appid in a
one to many relationship, you have a "chicken or egg" problem here! In
order for a record to exist in the chld table, one must already exist
in the parent table; you're saying that in order to add a record to
the parent table you must already have a record in the child table.
You can't have it both ways!

Am I misinterpreting the problem?

John W. Vinson [MVP]

I knew this might get hard to explain, but perhaps I'm just doing it wrong.
The business case: We have a vendor product, as access app, that has a
table which contains "application" data. We need to add more data
(columns) than their system allows for. SO when we add a new record, we
need to add additional information. So I set up a separate database with
the same key structure. What I need to do is add records to the new table
ONLY when one exists in the old table.

Access views "new records" in the new table the same, but for me there are
two cases: A new record that is asscciated with an existing record in the
Application table (The null side of an outter join, which I want to allow)
and a completely new record which shows up when the user scrolls down or
hits the >* button, which I do NOT want to allow. Essentially, I can't
figure out how to determine that latter condition. How do determine when
I'm at a *trully* new record condition in the master table (which is the
subform at this point). Maybe I need to reverse these forms or something.
I need to show the user every edittable record in the new form or perhaps
just insert a bunch of empty records with keys to sync up when I load the
form? DO I need to sync these manually and forget the master/child link?

The catch 22 is I was running into in syncing was associating a recordset
with the form: Copy the bookmark wasn't working when at EOF because I was
getting "no current record"

I humbly await your advice! Sorry for the delay...road trip!

 

[John W. Vinson]

I knew this might get hard to explain, but perhaps I'm just doing it wrong.
The business case: We have a vendor product, as access app, that has a
table which contains "application" data. We need to add more data
(columns) than their system allows for. SO when we add a new record, we
need to add additional information. So I set up a separate database with
the same key structure. What I need to do is add records to the new table
ONLY when one exists in the old table.

If you need more than the 255 fields that Access allows... or for that matter,
probably if you need more than 30 or so fields - you ARE doing something
wrong. What's the structure of your tables? I very strongly suspect that
you're "committing spreadsheet", storing one-to-many relationships by adding
more fields to the table, rather than having two tables in a one-to-many
relationship and adding new *rows*.

Access views "new records" in the new table the same, but for me there are
two cases: A new record that is asscciated with an existing record in the
Application table (The null side of an outter join, which I want to allow)
and a completely new record which shows up when the user scrolls down or
hits the >* button, which I do NOT want to allow. Essentially, I can't
figure out how to determine that latter condition. How do determine when
I'm at a *trully* new record condition in the master table (which is the
subform at this point). Maybe I need to reverse these forms or something.
I need to show the user every edittable record in the new form or perhaps
just insert a bunch of empty records with keys to sync up when I load the
form? DO I need to sync these manually and forget the master/child link?

It's certainly easier to have the master table on the master form, and the
child table on the subform. But creating empty "placeholder" records is
*NEVER* a good idea - they have a terrible habit of never getting filled in.

A one-to-one relationship still has a polarity - there's still a master record
and a child record.

The catch 22 is I was running into in syncing was associating a recordset
with the form: Copy the bookmark wasn't working when at EOF because I was
getting "no current record"

I humbly await your advice! Sorry for the delay...road trip!

I think your form problems stem from your table design. Again... if you have a
table with 256 fields, that's easily two hundred fields too many. Normalize!!!
"Fields are expensive, records are cheap!"

John W. Vinson [MVP]