Entering a subform

[Harr777]

I have a data entry from with a subform inside. If the primary key value in the main form is emptry, you can't enter the subform. Therefore, I created a button that assigns the primary key a value of zero in order to have something there in order to enter the subform.
The problem that I have now is that a new record with the 0 value saves when you enter the subform.
Can someone tell me a way to prevent the new record?

Thanks.

 

[MakeItSo]

Two possibilities:

Either the subform truly is one, i.e. the data on it is directly related/dependent on the records in the parent form.
--> Then it does not make sense to create a sub-record with having a main record first.

Or the subform is actually a second main form, then you should also treat it as such, i.e. have it as stand-alone form.

 

[DeanWilliams]

Hi,

Why do you want to enter the subform when there are no records related to the main form?

Dean.

 

[Harr777]

I should have mentioned, the subform is a continous subform with displays all of the related records.
ie. Main form is Meter Read entery form where you select a laction and machine. Sub form is continous form showing all of the meter reads for that location in the period selected in the main form.
I have it working (I even have a delete button form each record that works) The only problem that I have is the one that I posted.

 

[DeanWilliams]

As MakeItSo says then, it does not make sense to want to enter the subform if there is no main record.

Why do you want to access meter readings for a non-location?

Dean.

 

[Harr777]

Dean,

There is a location and read period selected, otherwise the subform is blank. The problem is if the serial number in the main form is blank. Its not needed for the subform because the purpose of the subform is to display the meter reads that match the location and read period.
When I enter the subform, the record tries to save in main form and throws and error because the new record does not have a serial number.
Like I said, I have a button that can make the serial number 0 and change the subform from inactive to active, but I need to undo the new record created when entering the subform.
Actually, the value must be different than any in the list box of serial numbers because otherwise I get a different error (because it is trying to save the record, it can say the it is a duplicate serial number)

 

[MakeItSo]

Sounds like you sometimes use your form(s) as a sort of calculator only.
In that case:
You could simply duplicate your forms,
unbind them from the table,
but leave the row sources as they are
as well as the calulations behind the field.

Would that solve it?

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[Harr777]

Andy,

I think that might work as far as preventing the error, however, the problem would be saving a record. The purpose of the main form is to save a new record.
I believe that it is possible to use your idea and save the fields to the table via VBA code.
I would have to research how to do that.

Rock on

 

[MakeItSo]

You can create a "Save" button on the new form with some code like this:

Dim strSQL as String

strSQL="INSERT INTO table (Serialno, field1, field2..) VALUES (" & DMax("Serialno", "table&quot+1 & ", '" & me!field1 & "', '" & me!field2 & "', '" ...

DoCmd.RunSQL strSQL

This will add a new record to your table with a new serialnumber...

However, I still doubt If it is any useful saving such a "Sub" record. Sounds like you could be getting loads of "orphan" records after a while, blowing the size of your db up without you being able to keeping track and order of these records.

Anyway - perhaps we are simply not understanding your intention... ;-)

 

[Harr777]

Andy,

I'll try to make it clear:
The main form saves meter reads for the chosen location and period acording to the selected serial number (displayed in a list box acording to the selected location).
The continous subform is there to display all of the meter reads for the selected period and selected location. Anyhow, regarding the sql statement; There are two primary keys. Would the sql statement need to reflect that? Also, could you tell me have I could verify via VBA code that the user is not entering a duplicate record?

Thanks
P.S. Don't forget to bring sunblock to the next rockfest!

 

[MakeItSo]

Ha! Yeah, I'll never forget THAT sunburn... B-)

Yes, your SQL should reflect the second PK:
e.g.
strSQL="INSERT INTO table (Serialno, Primary2, field2..) VALUES (" & DMax("Serialno", "table&quot+1 & ", '" & DMax("Primary2", "table&quot+1 & "', '" & me!field2 & "', '" ...

This will create new Serialnumber and second primary key as an increment to the highest present in table.

Considering the duplicate record. It's a bit tricky, but if you know exactly what makes a record a duplicate, you could search for a matching record in the table with a suitable SELECT ... WHERE:
[blue]
Dim rs as recordset
set rs=currentdb.openrecordset("SELECT field1, field2... " & _
" FROM table WHERE field1='" & me!field1 & "' AND field2='" & _
me!field2 & "' AND ..."

if rs.recordcount Then
msgbox "Record already exists!"
Do something here
[/blue]
Cheers,
MakeItSo

 

[rkasnick]

Sorry to weigh in here late, but it sound like you are trying to go about this backwards. If I were developing this app I would have my main form display (via multiple combo selection) the locations, periods and serial numbers with the sub form conditioned to either display specific reads for a serial number selected on the main form or allow entry of the new read.

 

[Harr777]

rkasnick,

Looks like you have a pretty good idea of the form. One thing to make clear: the list of serial numbers is based on the location. The user is entering the meter reads according to location. The sub form should show a list of all of the serial numbers that have a meter read so far for the selected location and read period. When you save a meter read in the main form, you see it enterd into the continous subform below. Idealy, when you are finished entering the meter reads for a location, you will see a read in the subform for each serial number in the list box.

When you enter the subform, the main form goes to save to record of the main form (I'd wish that I could prevent this (thats why makItso said to unbind main form)). Because the serialNum and readPeriod are both primary keys, if a seral number is selected has a read for the selected period, the main form says "you just entered a dublicated PK". If its blank, the main form says "null value in PK".

The solution that I hae is to assign a value to the serail when you press a button to activate the subform (like "0&quot.
But then I will have a new record that I don't want when the user enters the subform (PK is "0", readPeriod is whatever is surrenty selected). My idea was to do this, but undo to new record after the user leaves to subform, or closes the form. I'm not sure what VBA cod I would write to do that. So far, I'm going to go with makeItSo's idea of ubinding the main form and manually saving the record.

What do you think?

 

[rkasnick]

That is one solution, and since this is an active (I assume) database it is probably the best solution. However, if there is an opportunity to modify the table settings, I think the problem goes as deep as that level. The table permissions on the index are not set right for the purpose intended OR the primary key should be a composite key OR... There are a number of possible solutions, but here's one I have used in the past. When I had a main/sub form, it is possible to have the form properties set 'on open' that focus automatically goes to the first field of the subform, when the user leaves the subform focus is set to a field on the main form, then when that record is closed or updated, BOTH records are saved, main form first (that's the way access works) and no key violations are trapped. If you are truly stuck in a 'live' database, then that's the approach I would take at this point, OR try makItso's approach, either one may/should work, it's just a matter of your personal preference.