Stumped - How to Create a new Record if One Doesn't Exist..

[andmunn]

Hello,

I have a table which lists various divisions in the organization:

tblDivision
>pkDivisionID
>txtDivisionName

I now have a table which tracks whether there have been any "events" at the said division.

tblEvents
>pkEventID
>fkDivisionID
>EventDate
>EventPerson
>EventDescription

I have a combo box on my main form with a list of the divisions, which when i click a command button, iwant to take me to a form where

a) I can either add new events
b) edit existing events.

I created a new form based on a query of the above two tables - it works fine to view already existing events i manually inserted into the "tblEvents" table, but if an event doesn't already exist for the division, it doesn't work (because i assume no record exists). Therefore, when i click on this command button on the main form, if no record exists, i want it to create a new record using the combo box divisionID.

I tried the below, but it doesn't work (doesn't allow me to enter any new events):

Private Sub cmdEvents_Click()
strWhereCondition = "[pkDivID] = " & WhatUnit
DoCmd.OpenForm "frmEvents", , , strWhereCondition
End Sub

Any advice (note: "WhatUnit" is the combo box which contains a list of the divisions)?

Thanks for your help.
Andrew.

 

[boblarson]

Make sure you do not have the tblDivision table in the record source of the form for adding events.

 

[andmunn]

Hi Bob,

I do not have the tblDivision table in the record source of the form for adding events. My record source for "frmEvents" is "qryEvents" (which is a query built on tbldivision / tblevents).

Thanks,
Andrew.

 

[boblarson]

Hi Bob,

I do not have the tblDivision table in the record source of the form for adding events. My record source for "frmEvents" is "qryEvents" (which is a query built on tbldivision / tblevents).

Thanks,
Andrew.

Umm, tblDivision is part of qryEvents, right? So it is part of the recordsource of the form. Get rid of it. Take it out of qryEvents or use a new query with JUST tblEvents in it.

 

[andmunn]

Hi Bob,

Yes - it works now - can you explain to me why i can't reference tblDivision in the query?

Thanks for the quick response - if i want to now reference the "division name" in frmEvents, i assume i can do this through the use of fkDivID. I created a combo box with record source:

SELECT tblDivision.pkDivId, tblDivision.txtDivName from tblDivision

And it works fine - however, can i have it as a text box because i don't want to show the "down arrow"? I've never figured out how to do this.

Thanks again bob.

Andrew.

 

[boblarson]

I don't know that I can explain the why of why it doesn't update with that other table, but a combo box is normally the way to display that info. But you can use a text box in conjunction with that combo box if you are not going to be editing the data. You would simply have the combo box be hidden (Visible property set to NO) and then in the text box you would use the control source of something like:

=[Forms]![YourFormnameHere]![YourComboNameHere].Column(1)

where Column(1) means the second column (as it is zero-based).

 

[andmunn]

Hi Bob,

The visible / invisible solution works great for hte combo box. I now tried to add a record to frmEvents but i get this error:

"You cannot add or change a record because a related record is required in 'tblDivision'.

Hm - thouught it had it!

Andrew.

 

[boblarson]

Hi Bob,

The visible / invisible solution works great for hte combo box. I now tried to add a record to frmEvents but i get this error:

"You cannot add or change a record because a related record is required in 'tblDivision'.

Hm - thouught it had it!

Andrew.

Can you post a copy of the database? Make sure to:

1. Obscure any sensitive data.

2. Compact and Repair

3. Zip the file (if on Win XP or higher you can just right-click on the file and select SEND TO > COMPRESSED FOLDER.

4. Then post and we'll see if we can spot the problem.

 

[andmunn]

Hi Bob,

I Followed your steps. Not - the "command" button i'm getting the error when i try to add a new record is called "frozen assets details". You choose the unit at top, select "frozen asset details", and it wont let you add the record.

Thanks,
Adnrew.

 

[boblarson]

Okay, I got it.

1. You didn't need the combo box. You just needed to reference the other form (frmINTLScorecard) from the text box.

2. You need to add the division ID in to the record to save it. So, look at the VBA window for the form in the Before Update event to see what I did.

See attached revision to your database.

 

[andmunn]

Hi Bob,

It works like a charm now. I assume this code "copies" the pkDivID to the new record?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!fkDivID) Or Me!fkDivID = 0 Then
Me!fkDivID = CLng(Me.Text61)
End If
End Sub

 

[boblarson]

Yep, that's what it does alright.

 

[andmunn]

HI Bob,

For the "heading" of frmFrozenAssets, i want to show the name of the division, and not the FKDivID. When i change the textbox (text61) and add ".column(1)" to the end of the control source - it comes up with an error - how do i go about showing the name of the division rather then the ID?

Thanks so much again for your help.
Andrew.

 

[boblarson]

Okay, see this one. I just made the text box invisible (because I'm using it to add to the recordset if necessary) and added another text box on top using a DLookup.

 

[andmunn]

Ok Thanks - it's working as intended now. Thanks for the help. This DLookup / code you wrote i'm sure will come in handy in the future! Very helpful Bob - thanks so much.

Andrew.

 

[andmunn]

Hi Bob,

Well - i thought i could figure it out - but i can't! I am getting a simliar error when i click on the "Demarketed Clients" tab... I tried playing with the coding (given what i learned), but i can't seem to get it right. It wno't let me add new records when i select the division + click on the demarketed clients command button.

Any advice is appreciated.

Andrew.

 

[boblarson]

It's working for me. What exactly isn't working?

 

[andmunn]

Hi Bob,

WHen i try to add a new record (when i choose the business unit and click demarketed summaries) i get the following error:

"You cannot add or change a record because a related record is required in the table tlkpIdentifiedBy".

I know it's similar to the error i was having before, but i can't seem to fix it.

Andrew.

 

[boblarson]

Hi Bob,

WHen i try to add a new record (when i choose the business unit and click demarketed summaries) i get the following error:

"You cannot add or change a record because a related record is required in the table tlkpIdentifiedBy".

I know it's similar to the error i was having before, but i can't seem to fix it.

Andrew.

Strange, because I do the same and it worked wonderfully.

 

[andmunn]

Hm - now that is strange -

Are you sure you are clicking on "Demarketed Clients" and NOT "Frozen Assets" (the latter works fine). I get this error every time? Judging by the type of error i'm getting - do you know why / what could be the fix? (although, this might be difficult if you can't reproduce)

Andrew.