Subform not displaying data (1 Viewer)

[M Costumes]

I have a sub-form that is not displaying data when I change records on the main form. Both forms are based on tables. I've been changing records on the main form via a combo box--works great, displays everything on the main form that it should. The subform is blank--I can enter in new data, but it is not displaying any existing data related to the record displayed on the main form. I need the existing related data to display so that it can be updated (it's a rental inventory, so to mark a checkbox that an item was returned).

Master & child fields are identical.
Data entry: No on both.
Filter on Load: No on both.

Access crashed on me twice, and then started giving me some fun errors (return without gosub) on this form so I ran a decompile and compact & repair. The errors are gone, but it started doing this and I'm stumped.

 

[theDBguy]

Master & child fields are identical.

But it is correct? How is the main form table related to the subform table?

 

[M Costumes]

But it is correct? How is the main form table related to the subform table?

Should be. The PK of the main form (datatype autonumber) is related to the FK field of the same name (datatype number) in the subform in a 1:many.

 

[theDBguy]

Should be. The PK of the main form (datatype autonumber) is related to the FK field of the same name (datatype number) in the subform in a 1:many.

Since we can't see what you're looking at, we can't say what could be wrong with it. Can you post some screenshots or a demo copy of your db?

 

[M Costumes]

Since we can't see what you're looking at, we can't say what could be wrong with it. Can you post some screenshots or a demo copy of your db?

Here's a copy--it's all still in the works and contains dummy data.

The main form is frmAddRental.
Subform is subRentalInventory.

The involved tables are RentalDetails and RentalInventory

 

[bastanu]

Have a look at the updated file. The form\subform now works but I suggest if you take a very hard look at the entire database design. First of all using RecordID as generic PK and FK makes it very hard to follow the data flow. Also in the inventory table you have a couple fields set as Attachements; not only they are hard to use if you need to do reports or some other advanced programming, but they will quickly bloat your file.
Notice that I added the RentalID fields to both the form and subform (they were missing in both yet used as master\child linking). But the main cause of the issue was the record source of the subform which was based on two tables, the join eliminating the only records you had populated in the RentalInventory with a valid RentalID (not the default 0).
Cheers,

 

[M Costumes]

Have a look at the updated file. The form\subform now works but I suggest if you take a very hard look at the entire database design. First of all using RecordID as generic PK and FK makes it very hard to follow the data flow. Also in the inventory table you have a couple fields set as Attachements; not only they are hard to use if you need to do reports or some other advanced programming, but they will quickly bloat your file.
Notice that I added the RentalID fields to both the form and subform (they were missing in both yet used as master\child linking). But the main cause of the issue was the record source of the subform which was based on two tables, the join eliminating the only records you had populated in the RentalInventory with a valid RentalID (not the default 0).
Cheers,

Thank you so much! I am very new to this, so I'm sure there is quite a bit that I've done that is less than ideal--ha! I greatly appreciate you taking the time to look at my mess.

Because I learn best by repition/doing things, to make those changes to my original database to get the form/subform working I need to:
1) add a field for RentalID to both the main form & subform (and then hide them so they don't show up on the form view?)
2) change the record source of the subform to
SELECT RentalInventory.*
FROM RentalInventory;

 

[bastanu]

Seems right, but also look at the two text boxes to the right of the InventoryID combo, I've changed their control source as well.

Please post back if you run into any issues!

Cheers,
Vlad

 

[M Costumes]

Seems right, but also look at the two text boxes to the right of the InventoryID combo, I've changed their control source as well.

Please post back if you run into any issues!

Cheers,
Vlad

Thanks! I caught the two auto-filling text boxes as well. Thank you again, I greatly appreciate it. Cheers!

 

[bastanu]

You're very welcome, good luck with your project!
Cheers,

 

[M Costumes]

You're very welcome, good luck with your project!
Cheers,

Well, it was working when I quit last night. This morning it's giving me this error. I did have to run a decompile and compress & repair yesterday but maybe there is still something wrong? Or I didn't do that correctly? Starting to feel in over my head here. It happens after I try to add a new Inventory ID. This is the VBA for the combo box after event:

Option Compare Database

Private Sub cboInventoryID_AfterUpdate()
'Me.txtSetID = Me![cboInventoryID].Column(2)
'Me.txtRentalRate = Me![cboInventoryID].Column(3)

End Sub

Private Sub cboSelectSet_AfterUpdate()
Me.Refresh

End Sub

Private Sub Form_Load()
Me.Refresh

End Sub

 

[M Costumes]

@bastanu I just noticed you added an update query. Maybe that's what I'm missing when I tried to update my file.

I've rather given up, and saved a copy of your file so I'm working in that now since it well...works, ha! But I'm still curious if that's what I'm missing.

 

[bastanu]

No, the update query will not solve the problem. I suggest you split the database (using the built-in splitter) and connect the latest front-end to your real data and see what you get.

Cheers,
Vlad

 

[M Costumes]

No, the update query will not solve the problem. I suggest you split the database (using the built-in splitter) and connect the latest front-end to your real data and see what you get.

Cheers,
Vlad

I don't have any real data yet, but would splitting still be a good idea as I'm continuing to work out all these kinks?

 

[bastanu]

Yes, especially if you plan to deploy this in a multi-user environment.

 

[M Costumes]

Yes, especially if you plan to deploy this in a multi-user environment.

Great! I was planning on splitting it eventually, but wasn't sure if I needed to since it's just me right now. Cheers!

 

[bastanu]

I would also strongly recommend updating the tables by renaming all the generic RecordID with a more appropriate name (i.e. in tblGarmentType table rename the RecordID (PK) to GarmentTypeID or better GarmentTypePK) and in the tblInventory table GarmentType to GarmentTypeFK.
Doing this will make it much easier to build a proper database schema and follow the data flow logic within it.
Cheers,